
using System.Web.UI.HtmlControls;
using System;
using System.Configuration;
using System.Drawing;
using System.Data;
using System.Web.UI.WebControls;
using Microsoft.VisualBasic;
using System.Collections;
using System.Web.UI;
using System.Web;


using com.ddsc.common;
//using com.ddsc.database;
using DDSC.OracleADO;
using System.Security.Cryptography;
using System.Resources;
using System.Text;
using System.Xml;
using SMAS;
using System.IO;
using System.Runtime.InteropServices;
using System.Net.Mail;
using System.DirectoryServices;

//說明區域
//用途 : 提供Client 或 Server 取得資料庫資料或計算資料共用函式庫
//傳出格式 : DataSet

namespace smasb2b
{
    public class FunctionHandler : BasePage
    {

        DataSet dsReturn;
        DataBase DataBase = new DataBase();
        StringBuilder sb = new StringBuilder();

        //ftp use
        public const int SYNCHRONIZE = 0x100000;
        public const int WAIT_TIMEOUT = 0x102;
        [DllImport("kernel32", ExactSpelling = true, CharSet = CharSet.Ansi, SetLastError = true)]
        public static extern long WaitForSingleObject(long hHandle, long dwMilliseconds);
        [DllImport("kernel32", ExactSpelling = true, CharSet = CharSet.Ansi, SetLastError = true)]
        public static extern long OpenProcess(long dwDesiredAccess, long bInheritHandle, long dwProcessId);
        [DllImport("kernel32", ExactSpelling = true, CharSet = CharSet.Ansi, SetLastError = true)]
        public static extern long CloseHandle(long hObject);
        //ftp end


        //範例函式 : 依程式設計師使用命名
        public void getItemCodeInfo(string LangType)
        {
        }
        //查詢MATERIALd中英文品名
        //Tony : 改用com.ddsc.common.ddscDataSet
        //public DataSet Query_MATERIAL_CHNAME(ddscDataSet p_ddscDt)
        public DataSet Query_MATERIAL_CHNAME(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();
            DataSet ds = p_ddscDt.getDataSet();
            string GIFT_CODE = "";
            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                GIFT_CODE = ds.Tables[0].Rows[1]["GIFT_CODE"].ToString();
                sb.Append("Select ENGLISH_SHORTNAME,CHINESE_SHORTNAME From BAS_MATERIAL ");

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append("Where CODE = \'" + GIFT_CODE + "\'");
                sb.Append("Where CODE = :GIFT_CODE");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":GIFT_CODE", GIFT_CODE));

                sb.Append(" And status<> \'0\'");

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        //查詢MATERIALd中英文品名
        //Tony : 改用com.ddsc.common.ddscDataSet
        //public DataSet Query_MATERIAL_NAME(ddscDataSet p_ddscDt)
        public DataSet Query_MATERIAL_NAME(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();
            DataSet ds = p_ddscDt.getDataSet();
            string CODE = "";
            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                CODE = ds.Tables[0].Rows[1]["CODE"].ToString();
                sb.Append("Select ENGLISH_SHORTNAME,CHINESE_SHORTNAME,USE_TYPE_C,USE_TYPE_G,USE_TYPE_P From BAS_MATERIAL ");

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append("Where CODE = \'" + CODE + "\'");
                sb.Append("Where CODE = :CODE");
                sb.Append(" And status<> \'0\'");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":CODE", CODE));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        //查詢MATERIAL相關資料
        //Tony : 改用com.ddsc.common.ddscDataSet
        //public DataSet Query_MATERIAL(ddscDataSet p_ddscDt)
        public DataSet Query_MATERIAL(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();
            DataSet ds = p_ddscDt.getDataSet();
            try
            {
                // 查詢資料總筆數之 sql command

                string systemdate = DateTime.Today.ToString("yyyyMMdd");
                string Apply_no = System.Convert.ToString(ds.Tables[0].Rows[1]["Apply_no"]);

                sb.Append("select english_shortname,curr_unit_price,maxnum,request_status as purchase_status,request_date as purchase_date,use_type_p,use_type_c,use_type_g,class,subclass,replace_year,uniform_size");
                sb.Append(" from bas_material ");

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append("where code = \'" + Apply_no + "\'");
                sb.Append("where code = :Apply_no");
                sb.Append(" And status<> \'0\'");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":Apply_no", Apply_no));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb.ToString(), "material");
                dsReturn = DataBase.Query_CLASS(sb.ToString(), "material", m_arrParam);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }
        //查詢MATERIAL 地勤人員相關資料
        public DataSet Query_UNI_MATERIAL(com.ddsc.common.ddscDataSet p_ddscDt)
        {

            DataSet ds = p_ddscDt.getDataSet();
            try
            {
                // 查詢資料總筆數之 sql command

                string systemdate = DateTime.Today.ToString("yyyyMMdd");
                string Apply_no = System.Convert.ToString(ds.Tables[0].Rows[1]["Apply_no"]);
                string Apply_type = System.Convert.ToString(ds.Tables[0].Rows[1]["Apply_type"]);
                string Applicant = System.Convert.ToString(ds.Tables[0].Rows[1]["Applicant"]);
                string SelectCommandText = "";
                ArrayList m_arrParam = new ArrayList();
                dbAccess dba = new dbAccess();
                switch (Apply_type)
                {

                    case "N":

                        //Tony : 1.修改SQL語法使用參數，不組字串
                        //SelectCommandText = "select t1.code,t1.name,t1.curr_unit_price,t1.maxnum,t1.replace_year,t1.uni_size,t1.subclass,t1.purchase_status,t1.purchase_date, " 
                        //	+ " decode(t2.apply_qty,null,0,t2.apply_qty)as applyed_qty from (select code , english_shortname as name ,use_type_c,use_type_p,use_type_g, curr_unit_price,maxnum,replace_year,uni_size, " 
                        //	+ " subclass,purchase_status, decode(purchase_date,null,to_char(sysdate,\'yyyyMMdd\'),purchase_date) as purchase_date " 
                        //	+ " from material where class=\'U\' and status<>\'0\') t1," 
                        //	+ " (select code,apply_qty from apply_unifrom_log_tail where applicant=\'" + Applicant + "\') t2 " 
                        //	+ " where t1.code = t2.code(+) and t1.code = \'" + Apply_no + "\'";
                        SelectCommandText = "select t1.code,t1.name,t1.curr_unit_price,t1.maxnum,t1.replace_year,t1.uni_size,t1.subclass,t1.purchase_status,t1.purchase_date, "
                            + " decode(t2.apply_qty,null,0,t2.apply_qty)as applyed_qty from (select code , english_shortname as name ,use_type_c,use_type_p,use_type_g, curr_unit_price,maxnum,replace_year,uni_size, "
                            + " subclass,purchase_status, decode(purchase_date,null,to_char(sysdate,\'yyyyMMdd\'),purchase_date) as purchase_date "
                            + " from material where class=\'U\' and status<>\'0\') t1,"
                            + " (select code,apply_qty from apply_unifrom_log_tail where applicant= :Applicant) t2 "
                            + " where t1.code = t2.code(+) and t1.code = :Apply_no";
                        //Tony : 2.設定動態參數物件
                        m_arrParam.Add(dba.GetOracleParam(":Applicant", Applicant));
                        m_arrParam.Add(dba.GetOracleParam(":Apply_no", Apply_no));
                        break;
                    case "R":
                        //Tony : 1.修改SQL語法使用參數，不組字串
                        //SelectCommandText = "select t4.code,t4.name,t4.curr_unit_price,t4.replace_year,t4.uni_size,t4.subclass,t4.purchase_status,"
                        //    + " t4.purchase_date ,decode(t3.maxnum,null,0,t3.maxnum)maxnum,decode(t3.applyed_qty,null,0,t3.applyed_qty)applyed_qty from "
                        //    + " (select t1.code,t2.maxnum,t1.applyed_qty from (select code,apply_qty as applyed_qty from apply_unifrom_log_tail  where applicant=\'" + Applicant + "\') t1, "
                        //    + " (select code,sum(apply_qty) as maxnum from apply_unifrom_log_qty  where applicant=\'" + Applicant + "\' and due_date<=\'" + systemdate + "\' group by code) t2  "
                        //    + " where t1.code = t2.code(+)) t3, (select code,english_shortname as name , use_type_c,use_type_p,use_type_g ,curr_unit_price,replace_year,uni_size,subclass,purchase_status,  "
                        //    + " decode(purchase_date,null,to_char(sysdate,\'yyyyMMdd\'),purchase_date)  as purchase_date  from material "
                        //    + " where class=\'U\' and replace_year <> \' \' and status<>\'0\') t4  where t3.code(+) = t4.code and t3.applyed_qty <> \'0\' and t3.code=\'" + Apply_no + "\'";
                        SelectCommandText = "select t4.code,t4.name,t4.curr_unit_price,t4.replace_year,t4.uni_size,t4.subclass,t4.purchase_status,"
                            + " t4.purchase_date ,decode(t3.maxnum,null,0,t3.maxnum)maxnum,decode(t3.applyed_qty,null,0,t3.applyed_qty)applyed_qty from "
                            + " (select t1.code,t2.maxnum,t1.applyed_qty from (select code,apply_qty as applyed_qty from apply_unifrom_log_tail  where applicant= :Applicant) t1, "
                            + " (select code,sum(apply_qty) as maxnum from apply_unifrom_log_qty  where applicant= :Applicant and due_date<= :systemdate group by code) t2  "
                            + " where t1.code = t2.code(+)) t3, (select code,english_shortname as name , use_type_c,use_type_p,use_type_g ,curr_unit_price,replace_year,uni_size,subclass,purchase_status,  "
                            + " decode(purchase_date,null,to_char(sysdate,\'yyyyMMdd\'),purchase_date)  as purchase_date  from material "
                            + " where class=\'U\' and replace_year <> \' \' and status<>\'0\') t4  where t3.code(+) = t4.code and t3.applyed_qty <> \'0\' and t3.code= :Apply_no'";
                        //Tony : 2.設定動態參數物件
                        m_arrParam.Add(dba.GetOracleParam(":Applicant", Applicant));
                        m_arrParam.Add(dba.GetOracleParam(":systemdate", systemdate));
                        m_arrParam.Add(dba.GetOracleParam(":Apply_no", Apply_no));
                        break;

                    case "C":

                        //Tony : 1.修改SQL語法使用參數，不組字串
                        //SelectCommandText = "select material.code,name,curr_unit_price,maxnum,replace_year,uni_size,"
                        //    + " class,subclass,apply_unifrom_log_tail.code,apply_qty as applyed_qty,use_type_c,use_type_p,use_type_g from apply_unifrom_log_tail,"
                        //    + " (select code,english_shortname as name ,curr_unit_price,maxnum,replace_year,uni_size,"
                        //    + " class,subclass,use_type_c,use_type_p,use_type_g from material where class=\'U\' and status<>\'0\') material "
                        //    + " where applicant = \'" + Applicant + "\' and apply_unifrom_log_tail.code=\'" + Apply_no + "\' and apply_unifrom_log_tail.apply_qty>0 and apply_unifrom_log_tail.code = material.code(+)";

                        SelectCommandText = "select material.code,name,curr_unit_price,maxnum,replace_year,uni_size,"
                           + " class,subclass,apply_unifrom_log_tail.code,apply_qty as applyed_qty,use_type_c,use_type_p,use_type_g from apply_unifrom_log_tail,"
                           + " (select code,english_shortname as name ,curr_unit_price,maxnum,replace_year,uni_size,"
                           + " class,subclass,use_type_c,use_type_p,use_type_g from material where class=\'U\' and status<>\'0\') material "
                           + " where applicant = :Applicant and apply_unifrom_log_tail.code=:Apply_no and apply_unifrom_log_tail.apply_qty>0 and apply_unifrom_log_tail.code = material.code(+)";
                        //Tony : 2.設定動態參數物件
                        m_arrParam.Add(dba.GetOracleParam(":Applicant", Applicant));
                        m_arrParam.Add(dba.GetOracleParam(":Apply_no", Apply_no));
                        break;
                }
                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(SelectCommandText, "material");
                dsReturn = DataBase.Query_CLASS(SelectCommandText, "material", m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        //檢查料號庫存量
        public string CheckInventoryDetail(DataTable dt)
        {

            DataRow dr = default(DataRow); //Clinet 傳入的資料
            System.Data.DataRow dbdr = default(System.Data.DataRow); // 資料庫挑選出來的資料
            System.Data.DataRow[] logdr;
            string SQLCondition = "";
            DataSet ds = new DataSet();
            DataSet LogDs;
            string Applicant;
            string apply_type;
            try
            {
                //組合需要比對的料號SQL條件值
                foreach (DataRow tempLoopVar_dr in dt.Rows)
                {
                    dr = tempLoopVar_dr;
                    SQLCondition += "\'" + dr["code"].ToString() + "\',";
                }
                if (dt.Rows.Count > 0)
                {
                    //取得人事編號
                    Applicant = dt.Rows[0]["applicant"].ToString();
                    apply_type = dt.Rows[0]["apply_type"].ToString();
                    //組合執行SQL語法
                    string SelectCommandText = "select code,maxnum,purchase_status,purchase_date,class,decode(subclass,null,\'*\',subclass) as subclass,status,decode(invqty,null,0,invqty) as invqty,invcode "
                        + " from (select code,decode(maxnum,null,0,maxnum) as maxnum,request_status as purchase_status,"
                        + "request_date as purchase_date,class,subclass,status"
                        + " from bas_material where code in (" + SQLCondition.Substring(0, SQLCondition.Length - 1) + "))"
                        + ",(select decode((curr_qty-pren_qty-h_onway_qty),null,0,(curr_qty-pren_qty-h_onway_qty)) as invqty,curr_qty,code as invcode from inv_inventory"
                        + " where store_no=\'GENL\') "
                        + " where code = invcode(+)";
                    //Tony: 3.修改執行方法(新增傳入參數)
                    //ds = DataBase.Query_CLASS(SelectCommandText, "MATERIAL");
                    ArrayList m_arrParam = new ArrayList();
                    ds = DataBase.Query_CLASS(SelectCommandText, "MATERIAL", m_arrParam);

                }

                string ResponseXML = "<Error>";
                int i;
                bool IsError = default(bool);
                int DiffDay = 0;

                // NOTE START :  開始檢查 依輸入資料與資料庫進行資料比對

                foreach (DataRow tempLoopVar_dr in dt.Rows)
                {
                    dr = tempLoopVar_dr;
                    IsError = false;
                    dbdr = ds.Tables["MATERIAL"].Select("code = \'" + dr["CODE"].ToString() + "\'")[0];

                    //檢查1 是否庫存量不足
                    if (!IsError && dr["CODE"].ToString().Trim() != dr["APPLY_CODE"].ToString().Trim() && System.Convert.ToInt32(dr["applyqty"]) > System.Convert.ToInt32(dbdr["invqty"]) && System.Convert.ToString(dbdr["subclass"]) != "O")
                    {
                        IsError = true;
                        ResponseXML += System.Convert.ToString(addDetailErrorItem("txtREAL_CODE" + dr["id"].ToString(), dr["CODE"].ToString() + "庫存不足無法領料,目前庫存量為" + dbdr["invqty"].ToString(), "料號"));
                    }

                    //檢查2 料號是否被刪除
                    if (!IsError && dbdr["STATUS"] == "0")
                    {
                        IsError = true;
                        ResponseXML += System.Convert.ToString(addDetailErrorItem("txtREAL_CODE" + dr["id"].ToString(), "此料號已不存在", "料號"));
                    }

                    //檢查3 訂做料號是否停止請領
                    if (!IsError && dbdr["subclass"].ToString() == "O" && !Information.IsDBNull(dbdr["PURCHASE_DATE"]))
                    {
                        string year = System.Convert.ToString(dbdr["PURCHASE_DATE"].ToString().Substring(0, 4));
                        string month = System.Convert.ToString(dbdr["PURCHASE_DATE"].ToString().Substring(4, 2));
                        string day = System.Convert.ToString(dbdr["PURCHASE_DATE"].ToString().Substring(6, 2));
                        DiffDay = DateTime.Today.Subtract(new DateTime(System.Convert.ToInt32(year), System.Convert.ToInt32(month), System.Convert.ToInt32(day))).Days;
                        if (dbdr["PURCHASE_STATUS"] == "1" && DiffDay < 0)
                        {
                            IsError = true;
                            ResponseXML += System.Convert.ToString(addDetailErrorItem("txtREAL_CODE" + dr["id"].ToString(), "此料號尚未採購", "料號"));
                        }
                        if (dbdr["PURCHASE_STATUS"] == "2" && DiffDay >= 0)
                        {
                            IsError = true;
                            ResponseXML += System.Convert.ToString(addDetailErrorItem("txtREAL_CODE" + dr["id"].ToString(), "此料號停止採購", "料號"));
                        }
                    }

                    //檢查4 是否訂做類庫存
                    if (!IsError && System.Convert.ToInt32(dr["applyqty"]) > System.Convert.ToInt32(dbdr["invqty"]) && System.Convert.ToString(dbdr["subclass"]) == "O")
                    {
                        IsError = true;
                        ResponseXML += System.Convert.ToString(addDetailErrorItem("txtREAL_CODE" + dr["id"].ToString(), dr["CODE"].ToString() + "庫存不足無法領料,請確認是否簽收單未簽收,目前庫存量為" + dbdr["invqty"].ToString(), "料號"));
                    }

                }

                // NOTE END :

                ResponseXML += "</Error>";

                return ResponseXML;

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dt.Dispose();
                ds.Dispose();
            }
        }
        //add ErrorItem
        public dynamic addDetailErrorItem(string ID, string Message, string Name)
        {
            return string.Format("<Item id=\'{0}\' message=\'{1}\' name=\'{2}\' />", ID, Message, Name);
        }

        //查詢匯率
        public DataSet Query_EXCH_RATE(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string exch_type = "";
            string exch_date = "";
            string cuy1 = "";
            string cuy2 = "";
            StringBuilder sb = new StringBuilder();
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                // 查詢資料總筆數之 sql command

                exch_type = ds.Tables[0].Rows[1]["exch_type"].ToString();
                exch_date = ds.Tables[0].Rows[1]["exch_date"].ToString();
                cuy1 = ds.Tables[0].Rows[1]["CUY1"].ToString();
                cuy2 = ds.Tables[0].Rows[1]["CUY2"].ToString();


                sb.Append(" Select ex_rate EXCH_RATE From CRW_EXCHANGE_RATE ");
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where exch_type = \'" + exch_type + "\'");
                //sb.Append(" AND exch_date = \'" + exch_date.Substring(0, 6) + "\'");
                //sb.Append(" AND cur_dividend = \'" + cuy1 + "\'");
                //sb.Append(" AND cur_divisor = \'" + cuy2 + "\'");
                //sb.Append(" And status<> \'0\'");
                sb.Append(" Where exch_type = :exch_type");
                sb.Append(" AND exch_date = :exch_date");
                sb.Append(" AND cur_dividend = :cuy1");
                sb.Append(" AND cur_divisor = :cuy2");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":exch_type", exch_type));
                m_arrParam.Add(dba.GetOracleParam(":exch_date", exch_date.Substring(0, 6)));
                m_arrParam.Add(dba.GetOracleParam(":cuy1", cuy1));
                m_arrParam.Add(dba.GetOracleParam(":cuy2", cuy2));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }
        public DataSet Query_TWD_EXCH_RATE(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string exch_type;
            string exch_date = "";
            string cuy1;
            string cuy2 = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            StringBuilder sb = new StringBuilder();
            try
            {
                // 查詢資料總筆數之 sql command

                exch_type = ds.Tables[0].Rows[1]["exch_type"].ToString();
                exch_date = ds.Tables[0].Rows[1]["exch_date"].ToString();
                cuy1 = ds.Tables[0].Rows[1]["CUY1"].ToString();
                cuy2 = ds.Tables[0].Rows[1]["CUY2"].ToString();

                double sTWD = 0;
                double sTWD2 = 0;
                string sSql = "";

                //帶出兌換成美金的匯率
                sSql = "SELECT NVL(EX_RATE,0) EXCH_RATE FROM CRW_EXCHANGE_RATE ";
                sSql = sSql + " WHERE EXCH_TYPE = \'A\' and status<>\'0\' ";
                sSql = sSql + " AND CUR_DIVIDEND = \'TWD\'"; //cuy1
                sSql = sSql + " AND CUR_DIVISOR  = \'USD\'";
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sSql = sSql + " AND EXCH_DATE = \'" + exch_date.Substring(0, 6) + "\'";
                sSql = sSql + " AND EXCH_DATE = :exch_date";

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":exch_date", exch_date.Substring(0, 6)));

                DataBase.clearSB(sb);
                sb.Append(sSql);

                //Tony : 3.修改執行方法(新增傳入參數)
                //DataSet ds2 = DataBase.Query_CLASS(sb);
                DataSet ds2 = DataBase.Query_CLASS(sb, m_arrParam);

                if (!(ds2 == null) && ds2.Tables[0].Rows[0]["ReturnCode"] != "100" && ds2.Tables[0].Rows[0]["ReturnCode"] != "999")
                {
                    sTWD2 = System.Convert.ToDouble(ds2.Tables[1].Rows[0]["EXCH_RATE"]);
                }
                else
                {
                    sTWD2 = 0;
                }
                //
                if (cuy2 == "USD")
                {
                    sTWD = 1;
                }
                else
                {
                    sSql = "SELECT NVL(EX_RATE,0) EXCH_RATE FROM CRW_EXCHANGE_RATE ";
                    sSql = sSql + " WHERE EXCH_TYPE = \'A\' and status<>\'0\' ";
                    sSql = sSql + " AND CUR_DIVIDEND = \'USD\'";
                    sSql = sSql + " AND CUR_DIVISOR  = \'USD\'";
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //sSql = sSql + " AND CUR_DIVISOR  = \'" + cuy2 + "\'";
                    //sSql = sSql + " AND EXCH_DATE = \'" + exch_date.Substring(0, 6) + "\'";
                    sSql = sSql + " AND CUR_DIVISOR  = :cuy2";
                    sSql = sSql + " AND EXCH_DATE = :exch_date";

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Clear();
                    m_arrParam.Add(dba.GetOracleParam(":cuy2", cuy2));
                    m_arrParam.Add(dba.GetOracleParam(":exch_date", exch_date.Substring(0, 6)));

                    DataBase.clearSB(sb);
                    sb.Append(sSql);
                    //Tony : 3.修改執行方法(新增傳入參數)
                    //ds2 = DataBase.Query_CLASS(sb);
                    ds2 = DataBase.Query_CLASS(sb, m_arrParam);

                    if (!(ds2 == null) && ds2.Tables[0].Rows[0]["ReturnCode"] != "100" && ds2.Tables[0].Rows[0]["ReturnCode"] != "999")
                    {
                        sTWD = System.Convert.ToDouble(ds2.Tables[1].Rows[0]["EXCH_RATE"]);
                    }
                    else
                    {
                        sTWD = 0;
                    }
                }
                //sTWD = Mid(Format(sTWD * sTWD2, "#####.00000"), 1, Format(sTWD * sTWD2, "#####.00000").Length - 1)
                sTWD = double.Parse(Strings.Format(sTWD * sTWD2, "#####.0000"));

                DataBase.clearSB(sb);

                sb.Append(" Select " + System.Convert.ToString(sTWD) + " exch_rate From DUAL ");

                //Tony : 待處理
                m_arrParam.Clear();
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }
        //Query_CA_NO
        public DataSet Query_CA_NO(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string rvnu_date = "";
            string chked;
            StringBuilder sb = new StringBuilder();
            string sSql;
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();

            try
            {
                // 查詢資料總筆數之 sql command

                rvnu_date = ds.Tables[0].Rows[1]["rvnu_date"].ToString();
                chked = ds.Tables[0].Rows[1]["chked"].ToString();


                sb.Append(" Select ca_no From CRW_SALES_REVENUE_MASTER ");
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where rvnu_date = \'" + rvnu_date + "\'");
                sb.Append(" Where rvnu_date = :rvnu_date");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":rvnu_date", rvnu_date));

                if (chked == "N")
                {
                    sb.Append(" and  (CLEAR_MARK =\'N\' or clear_mark is null) And status<> \'0\' group by ca_no ");
                }
                else
                {
                    sb.Append(" and clear_mark=\'Y\' And status<> \'0\' group by ca_no ");
                }
                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        //查詢次類別
        //Public Function Query_Material_Class(ByVal p_ddscDt As ddscDataSet) As DataSet
        //    Dim ds As DataSet = p_ddscDt.getDataSet
        //    Dim str_class As String
        //    Try
        //        ' 查詢資料總筆數之 sql command
        //        Dim sb As StringBuilder = New StringBuilder
        //        str_class = ds.Tables(0).Rows(1)("class").ToString
        //        sb.Append("Select subclass,subclass_name From material_class ")
        //        sb.Append("Where class_level='2' and class = '" & str_class & "' order by subclass ")

        //        dsReturn = DataBase.Query_CLASS(sb)

        //    Catch ex As Exception
        //        Throw ex
        //    End Try

        //    Return dsReturn
        //End Function
        //查詢PURCHASE_MAST及PURCHASE_TAIL


        //  '===========================================================
        //加密函數
        //// 語法:S = Encrypt(欲加密的字串)
        ////===========================================================
        public string Encrypt(string data)
        {

            string Key = ConfigurationSettings.AppSettings["KEY"];
            string iv = ConfigurationSettings.AppSettings["VI"];
            byte[] buffer = Encoding.Default.GetBytes(data);
            MemoryStream ms = new MemoryStream();
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            CryptoStream encStream = new CryptoStream(ms, des.CreateEncryptor(Encoding.Default.GetBytes(Key), Encoding.Default.GetBytes(iv)), CryptoStreamMode.Write);
            encStream.Write(buffer, 0, buffer.Length);
            encStream.FlushFinalBlock();
            return Convert.ToBase64String(ms.ToArray());
        }

        ////===========================================================
        //// 解密函數
        //// 語法:S = Decrypt(欲解密的字串)
        ////===========================================================
        public string Decrypt(string data)
        {
            string Key = ConfigurationSettings.AppSettings["KEY"];
            string iv = ConfigurationSettings.AppSettings["VI"];
            byte[] buffer = Convert.FromBase64String((dynamic)data);
            MemoryStream ms = new MemoryStream();
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            CryptoStream encStream = new CryptoStream(ms, des.CreateDecryptor(Encoding.Default.GetBytes(Key), Encoding.Default.GetBytes(iv)), CryptoStreamMode.Write);
            encStream.Write(buffer, 0, buffer.Length);
            encStream.FlushFinalBlock();
            return Encoding.Default.GetString(ms.ToArray());
        }







        public DataSet Query_DELIVERY_TERM(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string delivery_no = "";
            string delivery_type = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();

            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                delivery_no = ds.Tables[0].Rows[1]["delivery_no"].ToString();
                delivery_type = ds.Tables[0].Rows[1]["delivery_type"].ToString();
                sb.Append("Select * From DELIVERY_TERM ");

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where DELIVERY_TYPE = \'" + delivery_type + "\'");
                //sb.Append(" AND DELIVERY_NO = \'" + delivery_no + "\'");
                sb.Append(" Where DELIVERY_TYPE = :delivery_type");
                sb.Append(" AND DELIVERY_NO = :delivery_no");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":delivery_type", delivery_type));
                m_arrParam.Add(dba.GetOracleParam(":delivery_no", delivery_no));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }
        public DataSet Query_PSN_OPEN_DATA(com.ddsc.common.ddscDataSet p_ddscDt)
        {

            DataSet ds = p_ddscDt.getDataSet();
            string employee_no = "";
            string SqlCommandText = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                employee_no = ds.Tables[0].Rows[1]["EMPLOYEE_NO"].ToString();

                //Tony : 1.修改SQL語法使用參數，不組字串
                //SqlCommandText = "select * from  PSN_OPEN_DATA where EMPLOYEE_NO=\'" + employee_no + "\'";
                SqlCommandText = "select * from  PSN_OPEN_DATA where EMPLOYEE_NO= :employee_no";

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":employee_no", employee_no));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(SqlCommandText, "PSN_OPEN_DATA");
                dsReturn = DataBase.Query_CLASS(SqlCommandText, "PSN_OPEN_DATA", m_arrParam);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        public DataSet Query_STD_NO(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string DEPT_DATE = "";
            string STD_NO = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                DEPT_DATE = ds.Tables[0].Rows[1]["DEPT_DATE"].ToString().Trim();
                STD_NO = ds.Tables[0].Rows[1]["STD_NO"].ToString().Trim();
                sb.Append("Select STD_NO From STD_LOAD ");

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where STD_NO =\'" + STD_NO + "\'");
                //sb.Append(" AND EFF_DATE <= \'" + DEPT_DATE + "\' AND INEFF_DATE > \'" + DEPT_DATE + "\' ");
                sb.Append(" Where STD_NO =:STD_NO");
                sb.Append(" AND EFF_DATE <= :DEPT_DATE AND INEFF_DATE > :DEPT_DATE ");

                sb.Append(" AND STATUS <> \'0\' ");
                sb.Append(" GROUP BY STD_NO ");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":STD_NO", STD_NO));
                m_arrParam.Add(dba.GetOracleParam(":DEPT_DATE", DEPT_DATE));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        public DataSet Query_STD_LOAD(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string STD_NO = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                STD_NO = ds.Tables[0].Rows[1]["STD_NO"].ToString().Trim();

                sb.Append("Select STD_NO From STD_LOAD ");

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where STD_NO =\'" + STD_NO + "\'");
                sb.Append(" Where STD_NO =:STD_NO");

                sb.Append(" AND STATUS <> \'0\' ");
                sb.Append(" GROUP BY STD_NO ");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":STD_NO", STD_NO));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        public DataSet INQuery_STD_NO(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string DEPT_DATE;
            string STD_NO = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                DEPT_DATE = ds.Tables[0].Rows[1]["DEPT_DATE"].ToString().Trim();
                STD_NO = ds.Tables[0].Rows[1]["STD_NO"].ToString().Trim();

                sb.Append("Select STND_LOAD_NO,EFF_DATE,EXP_DATE From CRW_STANDARD_LOAD ");
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where STND_LOAD_NO =\'" + STD_NO + "\'");
                sb.Append(" Where STND_LOAD_NO = :STD_NO");
                sb.Append(" AND STATUS <> \'0\' ");
                sb.Append(" GROUP BY STND_LOAD_NO,EFF_DATE,EXP_DATE ");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":STD_NO", STD_NO));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        public DataSet Query_DEPARTMENT(com.ddsc.common.ddscDataSet p_ddscDt)
        {

            DataSet ds = p_ddscDt.getDataSet();
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                string department_no = ds.Tables[0].Rows[1]["department_no"].ToString().Trim();

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append("select null  from department where department_no=\'" + department_no + "\'");
                sb.Append("select null  from department where department_no= :department_no");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":department_no", department_no));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb.ToString(), "department");
                dsReturn = DataBase.Query_CLASS(sb.ToString(), "department", m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;

        }


        // 取得海關關帳數量
        //Creator by Titan
        public int GetCLOSE_QTY(string STORE_NO, string CODE, string PERIOD)
        {
            int close_qty = 0;
            string Customs_Period = "";
            string From_YYYYMM = "";
            string Till_YYYYMM = "";
            DataSet ds_close = default(DataSet);
            DataSet ds_mast = default(DataSet);
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                //取得海關關帳數量資料
                //Tony : 1.修改SQL語法使用參數，不組字串
                //string SelectCommandText = "SELECT PERIOD, NVL(CLOSE_QTY,0) CLOSE_QTY FROM CUSTOMS_CLOSE"
                //    + " WHERE STORE_NO = \'" + STORE_NO + "\' AND CODE = \'" + CODE + ("\'"
                //    + "  AND PERIOD = ( SELECT MIN(PERIOD) FROM CUSTOMS_CLOSE"
                //    + " WHERE STORE_NO = \'") + STORE_NO + "\' AND CODE = \'" + CODE + "\')";
                string SelectCommandText = "SELECT PERIOD, NVL(CLOSE_QTY,0) CLOSE_QTY FROM CUSTOMS_CLOSE"
                    + " WHERE STORE_NO = :STORE_NO AND CODE = :CODE"
                    + ("  AND PERIOD = ( SELECT MIN(PERIOD) FROM CUSTOMS_CLOSE"
                    + " WHERE STORE_NO = \'") + ":STORE_NO\' AND CODE = :CODE)";

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":STORE_NO", STORE_NO));
                m_arrParam.Add(dba.GetOracleParam(":CODE", CODE));

                //Tony : 3.修改執行方法(新增傳入參數)
                //ds_close = DataBase.Query_CLASS(SelectCommandText, "CUSTOMS_CLOSE");
                ds_close = DataBase.Query_CLASS(SelectCommandText, "CUSTOMS_CLOSE", m_arrParam);

                if (!(ds_close == null) && ds_close.Tables[0].Rows[0]["ReturnCode"] != "100" && ds_close.Tables[0].Rows[0]["ReturnCode"] != "999")
                {
                    Customs_Period = ds_close.Tables["CUSTOMS_CLOSE"].Rows[0]["PERIOD"].ToString();
                    close_qty = int.Parse(ds_close.Tables["CUSTOMS_CLOSE"].Rows[0]["CLOSE_QTY"].ToString());

                    if (System.Convert.ToInt32(PERIOD) < System.Convert.ToInt32(Customs_Period))
                    {
                        From_YYYYMM = PERIOD;
                        Till_YYYYMM = Customs_Period;
                    }
                    else
                    {
                        From_YYYYMM = Customs_Period;
                        Till_YYYYMM = PERIOD;
                    }

                    //取得海關交易資料
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //SelectCommandText = "SELECT NVL(SUM(TRANS_QTY),0) TRANS_QTY, IN_OUT FROM CUSTOMS_TRANS_MAST A, CUSTOMS_TRANS_TAIL B"
                    //    + " WHERE A.STORE_NO = \'" + STORE_NO + ("\' AND A.SHEET_NO = B.SHEET_NO "
                    //    + " AND A.STORE_NO = B.STORE_NO AND B.CODE = \'") + CODE + ("\'"
                    //    + " AND SUBSTR(IN_OUT_DATE,1,6) BETWEEN \'") + From_YYYYMM + "\' AND \'" + Till_YYYYMM + ("\'"
                    //    + " GROUP BY IN_OUT");
                    SelectCommandText = "SELECT NVL(SUM(TRANS_QTY),0) TRANS_QTY, IN_OUT FROM CUSTOMS_TRANS_MAST A, CUSTOMS_TRANS_TAIL B"
                        + " WHERE A.STORE_NO = :STORE_NO" + (" AND A.SHEET_NO = B.SHEET_NO "
                        + " AND A.STORE_NO = B.STORE_NO AND B.CODE = \'") + ":CODE" + ("\'"
                        + " AND SUBSTR(IN_OUT_DATE,1,6) BETWEEN \'") + ":From_YYYYMM\' AND :Till_YYYYMM" + (" GROUP BY IN_OUT");

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Clear();
                    m_arrParam.Add(dba.GetOracleParam(":STORE_NO", STORE_NO));
                    m_arrParam.Add(dba.GetOracleParam(":CODE", CODE));
                    m_arrParam.Add(dba.GetOracleParam(":From_YYYYMM", From_YYYYMM));
                    m_arrParam.Add(dba.GetOracleParam(":Till_YYYYMM", Till_YYYYMM));

                    //Tony : 3.修改執行方法(新增傳入參數)
                    //ds_mast = DataBase.Query_CLASS(SelectCommandText, "trans_mast");
                    ds_mast = DataBase.Query_CLASS(SelectCommandText, "trans_mast", m_arrParam);

                    DataRow dr = default(DataRow);
                    foreach (DataRow tempLoopVar_dr in ds_mast.Tables["trans_mast"].Rows)
                    {
                        dr = tempLoopVar_dr;
                        if ((string)dr["IN_OUT"] == "I")
                        {
                            close_qty = close_qty - System.Convert.ToInt32(dr["TRANS_QTY"]);
                        }
                        else //O
                        {
                            close_qty = close_qty + System.Convert.ToInt32(dr["TRANS_QTY"]);
                        }
                    }
                    ds_mast.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                ds_close.Dispose();
            }

            return close_qty;

        }

        #region <NOTE : 提供制服模組相關函式 >

        public class UNI
        {
            DataSet dsReturn;
            DataBase DataBase = new DataBase();
            //取得制服請領紀錄明細檔
            public DataTable Query_APPLY_UNIFROM_LOG_TAIL(Array ArrOfCode, string Applicant)
            {
                int i = 0;
                string SetWhereString = string.Empty;
                DataSet dsReturn = default(DataSet);
                ArrayList m_arrParam = new ArrayList();
                dbAccess dba = new dbAccess();
                try
                {
                    for (i = 0; i <= ArrOfCode.Length - 1; i++)
                    {
                        SetWhereString += "\'" + ArrOfCode.GetValue(i).ToString() + "\',";
                    }
                    if (SetWhereString.Length > 1)
                    {
                        SetWhereString = SetWhereString.Substring(0, SetWhereString.Length - 1);
                    }
                    else
                    {
                        SetWhereString = "\'\'";
                    }

                    // 查詢資料總筆數之 sql command
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //string SelectCommandText = "select log.code as logcode,material.code as code,material.maxnum,replace_year,decode(apply_qty,null,0,apply_qty) apply_qty from"
                    //    + "(select code,apply_qty from uni_unifrom_apply_details where applicant=\'" + Applicant + "\') log ,"
                    //    + "(select code,replace_year,decode(maxnum,null,0,maxnum) maxnum from bas_material where code in (" + SetWhereString + ")) material "
                    //    + "where log.code(+) = material.code";
                    string SelectCommandText = "select log.code as logcode,material.code as code,material.maxnum,replace_year,decode(apply_qty,null,0,apply_qty) apply_qty from"
                        + "(select code,apply_qty from uni_unifrom_apply_details where applicant= :Applicant) log ,"
                        + "(select code,replace_year,decode(maxnum,null,0,maxnum) maxnum from bas_material where code in (:SetWhereString)) material "
                        + "where log.code(+) = material.code";

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":Applicant", Applicant));
                    m_arrParam.Add(dba.GetOracleParam(":SetWhereString", SetWhereString));

                    //Tony : 3.修改執行方法(新增傳入參數)
                    //dsReturn = DataBase.Query_CLASS(SelectCommandText, "Apply_log_tail");
                    dsReturn = DataBase.Query_CLASS(SelectCommandText, "Apply_log_tail", m_arrParam);

                }
                catch (Exception ex)
                {
                    throw (ex);
                }

                return dsReturn.Tables["Apply_log_tail"];

            }

            public DataTable Query_CREW_ALL_APPLY_LOG(string apply_no)
            {
                DataSet dsReturn = default(DataSet);
                ArrayList m_arrParam = new ArrayList();
                dbAccess dba = new dbAccess();
                try
                {

                    // 查詢資料總筆數之 sql command
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //string SelectCommandText = "select applicant,chinese_name from uni_crew_group_apply_log,"
                    //    + " (select employee_no,chinese_name from psn_open_data where trans_mark1=\'C\') psn_open_data "
                    //    + "where apply_no =\'" + apply_no + "\' and uni_crew_group_apply_log.applicant = psn_open_data.employee_no(+)";
                    string SelectCommandText = "select applicant,chinese_name from uni_crew_group_apply_log,"
                        + " (select employee_no,chinese_name from psn_open_data where trans_mark1=\'C\') psn_open_data "
                        + "where apply_no = :apply_no and uni_crew_group_apply_log.applicant = psn_open_data.employee_no(+)";

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":apply_no", apply_no));

                    //Tony : 3.修改執行方法(新增傳入參數)
                    //dsReturn = DataBase.Query_CLASS(SelectCommandText, "crew_all_apply_log");
                    dsReturn = DataBase.Query_CLASS(SelectCommandText, "crew_all_apply_log", m_arrParam);

                }
                catch (Exception ex)
                {
                    throw (ex);
                }

                return dsReturn.Tables["crew_all_apply_log"];
            }

            //取得制服請領紀錄明細檔
            public bool IsExistLogMast(string Applicant)
            {
                ArrayList m_arrParam = new ArrayList();
                dbAccess dba = new dbAccess();
                try
                {
                    // 查詢資料總筆數之 sql command
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //string SelectCommandText = "select Applicant from uni_unifrom_apply_master where applicant=\'" + Applicant + "\'";
                    string SelectCommandText = "select Applicant from uni_unifrom_apply_master where applicant=:Applicant";

                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":Applicant", Applicant));

                    //Tony : 3.修改執行方法(新增傳入參數)
                    //dsReturn = DataBase.Query_CLASS(SelectCommandText, "Apply_log_mast");
                    dsReturn = DataBase.Query_CLASS(SelectCommandText, "Apply_log_mast", m_arrParam);
                }
                catch (Exception ex)
                {
                    throw (ex);
                }

                return dsReturn.Tables["Apply_log_mast"].Rows.Count > 0;
            }


            public DataTable Query_APPLY_UNIFROM_LOG_QTY(Array ArrOfCode, string applicant)
            {
                ArrayList m_arrParam = new ArrayList();
                dbAccess dba = new dbAccess();
                try
                {
                    string SetWhereString = "";
                    int i = 0;
                    for (i = 0; i <= ArrOfCode.Length - 1; i++)
                    {
                        SetWhereString += "\'" + ArrOfCode.GetValue(i).ToString() + "\',";
                    }
                    if (SetWhereString.Length > 1)
                    {
                        SetWhereString = SetWhereString.Substring(0, SetWhereString.Length - 1);
                    }
                    else
                    {
                        SetWhereString = "\'\'";
                    }

                    // 查詢資料總筆數之 sql command
                    //Tony : 1.修改SQL語法使用參數，不組字串
                    //string SelectCommandText = "select code,seq,due_date from uni_unifrom_apply_quantity where applicant=\'" + applicant + "\' and code in (" + SetWhereString + ") order by due_date";
                    string SelectCommandText = "select code,seq,due_date from uni_unifrom_apply_quantity where applicant= :applicant and code in (:SetWhereString) order by due_date";
                    //Tony : 2.設定動態參數物件
                    m_arrParam.Add(dba.GetOracleParam(":applicant", applicant));
                    m_arrParam.Add(dba.GetOracleParam(":SetWhereString", SetWhereString));

                    //Tony : 3.修改執行方法(新增傳入參數)
                    //dsReturn = DataBase.Query_CLASS(SelectCommandText, "Apply_log_qty");
                    dsReturn = DataBase.Query_CLASS(SelectCommandText, "Apply_log_qty", m_arrParam);
                }
                catch (Exception ex)
                {
                    throw (ex);
                }

                return dsReturn.Tables["Apply_log_qty"];

            }

            public dynamic GetCloseYear(int ReplaceYear)
            {
                int SystemYear = DateTime.Today.Year;
                int CloseYear = 2005;
                try
                {
                    while (true)
                    {
                        if ((CloseYear <= SystemYear) && (SystemYear - CloseYear <= ReplaceYear - 1))
                        {
                            return CloseYear.ToString();
                        }
                        CloseYear = CloseYear + ReplaceYear;
                    }
                }
                catch (Exception)
                {
                    return "0";
                }
            }

            //Tony : 待處理
            //<NOTE : 取得執行扣庫存SP的SQL COMMAND>
            public dynamic getSP_trans_deal(string CARD_ID, string P_TRANS_NO, string P_CONFIRM_DATE, string P_ACTION, string P_STATUS, string P_USER, string P_DEPT)
            {
                if (CARD_ID != "C" && CARD_ID != "RC")
                {
                    return string.Format("Begin g_trans_deal(\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\',\'{5}\'); end;", P_TRANS_NO, P_CONFIRM_DATE, P_ACTION, P_STATUS, P_USER, P_DEPT);
                }
                else
                {
                    return string.Format("Begin c_trans_deal(\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\',\'{5}\'); end;", P_TRANS_NO, P_CONFIRM_DATE, P_ACTION, P_STATUS, P_USER, P_DEPT);
                }
            }

            //<NOTE : 設定請領紀錄主檔欄位>
            public void setLogMainTableColumn(com.ddsc.common.ddscDataTable ddscDtLM)
            {
                ddscDtLM.addColumn("action", "string"); // set ColumnName
                ddscDtLM.addColumn("applicant", "string");
                ddscDtLM.addColumn("apply_date", "string");
                ddscDtLM.addColumn("use_user", "string");
                ddscDtLM.addColumn("name", "string");
                ddscDtLM.addColumn("status", "string");
                ddscDtLM.addColumn("crt_id", "string");
                ddscDtLM.addColumn("crt_date", "string");
                ddscDtLM.addColumn("crt_time", "string");
                ddscDtLM.addColumn("upd_id", "string");
                ddscDtLM.addColumn("upd_date", "string");
                ddscDtLM.addColumn("upd_time", "string");
            }
            //<NOTE : 設定請領紀錄明細檔欄位>
            public void setLogDetailTableColumn(com.ddsc.common.ddscDataTable ddscDtLD)
            {
                ddscDtLD.addColumn("action", "string"); // set ColumnName
                ddscDtLD.addColumn("applicant", "string");
                ddscDtLD.addColumn("code", "string");
                ddscDtLD.addColumn("apply_qty", "decimal");
                ddscDtLD.addColumn("return_qty", "decimal");
                ddscDtLD.addColumn("remark", "string");
                ddscDtLD.addColumn("status", "string");
                ddscDtLD.addColumn("crt_id", "string");
                ddscDtLD.addColumn("crt_date", "string");
                ddscDtLD.addColumn("crt_time", "string");
                ddscDtLD.addColumn("upd_id", "string");
                ddscDtLD.addColumn("upd_date", "string");
                ddscDtLD.addColumn("upd_time", "string");
            }
            //<NOTE : 設定請領紀錄明細數量欄位>
            public void setLogQtyTableColumn(com.ddsc.common.ddscDataTable ddscDTLQ)
            {
                ddscDTLQ.addColumn("action", "string"); // set ColumnName
                ddscDTLQ.addColumn("applicant", "string");
                ddscDTLQ.addColumn("code", "string");
                ddscDTLQ.addColumn("seq", "string");
                ddscDTLQ.addColumn("due_date", "string");
                ddscDTLQ.addColumn("apply_qty", "decimal");
                ddscDTLQ.addColumn("status", "string");
                ddscDTLQ.addColumn("crt_id", "string");
                ddscDTLQ.addColumn("crt_date", "string");
                ddscDTLQ.addColumn("crt_time", "string");
                ddscDTLQ.addColumn("upd_id", "string");
                ddscDTLQ.addColumn("upd_date", "string");
                ddscDTLQ.addColumn("upd_time", "string");
            }

            //<NOTE : 設定交易主檔欄位>
            public void setTranMastTableColumn(com.ddsc.common.ddscDataTable ddscDTTM)
            {
                ddscDTTM.addColumn("action", "string"); // set ColumnName
                ddscDTTM.addColumn("trans_no", "string");
                ddscDTTM.addColumn("department_no", "string");
                ddscDTTM.addColumn("card_id", "string");
                ddscDTTM.addColumn("confirm_date", "string");
                ddscDTTM.addColumn("trans_date", "string");
                ddscDTTM.addColumn("store_from", "string");
                ddscDTTM.addColumn("issue_type", "string");
                ddscDTTM.addColumn("user_id", "string");
                ddscDTTM.addColumn("esti", "string");
                ddscDTTM.addColumn("remark", "string");
                ddscDTTM.addColumn("dept_approve", "string");
                ddscDTTM.addColumn("admin_approve", "string");
                ddscDTTM.addColumn("applicant", "string");
                ddscDTTM.addColumn("g_type", "string");
                ddscDTTM.addColumn("crt_id", "string");
                ddscDTTM.addColumn("crt_date", "string");
                ddscDTTM.addColumn("crt_time", "string");
                ddscDTTM.addColumn("upd_id", "string");
                ddscDTTM.addColumn("upd_date", "string");
                ddscDTTM.addColumn("upd_time", "string");
            }

            //<NOTE : 設定交易明細檔欄位>
            public void setTranDetailTableColumn(com.ddsc.common.ddscDataTable ddscDTTD)
            {
                ddscDTTD.addColumn("action", "string"); // set ColumnName
                ddscDTTD.addColumn("trans_no", "string");
                ddscDTTD.addColumn("trans_seq", "string");
                ddscDTTD.addColumn("code", "string");
                ddscDTTD.addColumn("trans_qty", "string");
                ddscDTTD.addColumn("trans_amt", "decimal");
                ddscDTTD.addColumn("original_trans_no", "string");
                ddscDTTD.addColumn("original_trans_amt", "decimal");
                ddscDTTD.addColumn("status", "string");
                ddscDTTD.addColumn("crt_id", "string");
                ddscDTTD.addColumn("crt_date", "string");
                ddscDTTD.addColumn("crt_time", "string");
                ddscDTTD.addColumn("upd_id", "string");
                ddscDTTD.addColumn("upd_date", "string");
                ddscDTTD.addColumn("upd_time", "string");
            }
            //<NOTE : 設定庫存檔欄位>
            public void setInvTableColumn(com.ddsc.common.ddscDataTable ddscDTIM)
            {
                ddscDTIM.addColumn("action", "string");
                ddscDTIM.addColumn("store_no", "string");
                ddscDTIM.addColumn("code", "string");
                ddscDTIM.addColumn("pren_qty", "decimal");
                ddscDTIM.addColumn("curr_qty", "decimal");
                ddscDTIM.addColumn("upd_id", "string");
                ddscDTIM.addColumn("upd_date", "string");
                ddscDTIM.addColumn("upd_time", "string");
            }
        }

        #endregion

        public string Query_APPLY_UNIFROM_LOG_QTY(com.ddsc.common.ddscDataSet p_ddscDt)
        {

            DataSet ds = p_ddscDt.getDataSet();
            string applicant = System.Convert.ToString(ds.Tables[0].Rows[1]["applicant"].ToString().Trim());
            string code = System.Convert.ToString(ds.Tables[0].Rows[1]["code"].ToString().Trim());
            string apply_date = System.Convert.ToString(ds.Tables[0].Rows[1]["apply_date"].ToString().Trim());
            string apply_type = System.Convert.ToString(ds.Tables[0].Rows[1]["apply_type"].ToString().Trim());
            string total_apply_qty = "";
            string SelectCommandText = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                switch (apply_type)
                {
                    case "R":
                        //Tony : 1.修改SQL語法使用參數，不組字串
                        //SelectCommandText = "select sum(apply_qty) as total_apply_qty from apply_unifrom_log_qty where applicant=\'" + applicant + "\' and code=\'" + code + "\' and due_date<=\'" + apply_date + "\'";
                        SelectCommandText = "select sum(apply_qty) as total_apply_qty from apply_unifrom_log_qty where applicant= :applicant and code= :code and due_date<=:apply_date";
                        //Tony : 2.設定動態參數物件
                        m_arrParam.Add(dba.GetOracleParam(":applicant", applicant));
                        m_arrParam.Add(dba.GetOracleParam(":code", code));
                        m_arrParam.Add(dba.GetOracleParam(":apply_date", apply_date));
                        break;
                    case "N":
                        //Tony : 1.修改SQL語法使用參數，不組字串
                        //SelectCommandText = "select apply_qty as total_apply_qty from apply_unifrom_log_tail where applicant=\'" + applicant + "\' and code=\'" + code + "\'";
                        SelectCommandText = "select apply_qty as total_apply_qty from apply_unifrom_log_tail where applicant = :applicant and code = :code";
                        //Tony : 2.設定動態參數物件
                        m_arrParam.Add(dba.GetOracleParam(":applicant", applicant));
                        m_arrParam.Add(dba.GetOracleParam(":code", code));
                        break;
                }

                //執行SQL語法取得已請領數量
                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(SelectCommandText, "apply_unifrom_log_qty");
                dsReturn = DataBase.Query_CLASS(SelectCommandText, "apply_unifrom_log_qty", m_arrParam);

                if (dsReturn.Tables["apply_unifrom_log_qty"].Rows.Count > 0)
                {
                    total_apply_qty = dsReturn.Tables["apply_unifrom_log_qty"].Rows[0]["total_apply_qty"].ToString();
                }

                if (total_apply_qty == "")
                {
                    total_apply_qty = "0";
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dsReturn.Dispose();
            }

            return string.Format("<ResponseXML name=\'total_apply_qty\'>{0}</ResponseXML>", total_apply_qty);

        }

        ////===========================================================
        //// 在變動長度之字串前後填補指定字元函式
        //// 語法:V = PadFullChar(字串,長度,填補字元,選項)
        //// 備註:選項 1:補前面 2:補後面 Default:補後面
        ////===========================================================
        public string GetPadFullChar(string sNumString, int nStrLength, char ch, string sOption)
        {

            string strReturn = "";
            string strLen = "";
            int intLen = 0;

            if (sNumString.ToString().Trim() == "")
            {
                return strReturn.PadLeft(nStrLength, ch);
            }

            intLen = System.Convert.ToInt32(System.Text.Encoding.Default.GetBytes(sNumString).Length);

            if (sNumString.Length != 0)
            {
                if (nStrLength < intLen)
                {
                    byte[] bString = System.Text.Encoding.Default.GetBytes(sNumString);
                    byte[] btString = new byte[nStrLength + 1];
                    int i = 0;
                    for (i = 0; i <= bString.Length; i++)
                    {
                        if (i == nStrLength)
                        {
                            btString[i] = bString[i];
                        }
                    }

                    strReturn = System.Text.Encoding.Default.GetString(btString);
                    return strReturn;

                }

                switch (sOption)
                {
                    case "1":
                        //字串前面補字元
                        strReturn = System.Convert.ToString(strLen.PadLeft(nStrLength - intLen, ch) + sNumString.ToString());
                        break;
                    case "2":
                        //字串後面補字元
                        strReturn = System.Convert.ToString(sNumString.ToString() + strLen.PadRight(nStrLength - intLen, ch));
                        break;
                    default:
                        //字串後面補字元
                        strReturn = System.Convert.ToString(sNumString.ToString() + strLen.PadRight(nStrLength - intLen, ch));
                        break;
                }

            }

            return strReturn;

        }

        public void EditReturnDataSetInfo(DataSet ds, string columnName, string Text)
        {
            try
            {
                ds.Tables["info"].Rows[0].BeginEdit();
                ds.Tables["info"].Rows[0][columnName] = Text;
                ds.Tables["info"].Rows[0].EndEdit();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        //-----------------------------'
        //取得員工編號
        //傳入: 員工編號 (Employee_NO)
        //更新日期 : 20050526 add by titan
        //-----------------------------'
        //Function CreateEmployee(ByVal Employee_NO As String) As Employee
        //    Dim Employee As Employee = New Employee
        //    Dim ReturnDS As DataSet
        //    Dim SelectCommand As String = String.Format("select psn_open_data.*,decode(trans_mark1,null,'G',trans_mark1) as Gtype from  psn_open_data where employee_no = '{0}'", Employee_NO)
        //    Try
        //        ReturnDS = DataBase.Query_CLASS(SelectCommand, "psn_open_data")
        //        Dim Row As Integer

        //        If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables("info").Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables("info").Rows(0)("ReturnCode") <> "999") Then
        //            Row = ReturnDS.Tables("psn_open_data").Rows.Count - 1
        //            Employee.ID = ReturnDS.Tables("psn_open_data").Rows(Row)("employee_no").ToString
        //            Employee.Department = ReturnDS.Tables("psn_open_data").Rows(Row)("department").ToString
        //            Employee.Gtype = ReturnDS.Tables("psn_open_data").Rows(Row)("Gtype").ToString
        //            Employee.Positon = ReturnDS.Tables("psn_open_data").Rows(Row)("Position_code").ToString
        //            Employee.IsManger = ReturnDS.Tables("psn_open_data").Rows(Row)("Manager") = "Y"
        //            Employee.Name = ReturnDS.Tables("psn_open_data").Rows(Row)("chinese_name").ToString
        //            Employee.English_Name = ReturnDS.Tables("psn_open_data").Rows(Row)("english_name").ToString
        //            Employee.Position_Des = ReturnDS.Tables("psn_open_data").Rows(Row)("position_desc").ToString
        //            Employee.Pos_Seq = ReturnDS.Tables("psn_open_data").Rows(Row)("pos_seq").ToString
        //            Employee.Email = ReturnDS.Tables("psn_open_data").Rows(Row)("email").ToString
        //            Employee.Employee_Name = ReturnDS.Tables("psn_open_data").Rows(Row)("employee_name").ToString
        //            Employee.NickName = ReturnDS.Tables("psn_open_data").Rows(Row)("nickname").ToString
        //            Employee.Effective_date = ReturnDS.Tables("psn_open_data").Rows(Row)("Effective_date").ToString
        //            Employee.INEffective_date = ReturnDS.Tables("psn_open_data").Rows(Row)("INEffective_date").ToString
        //            Employee.Ext_No = ReturnDS.Tables("psn_open_data").Rows(Row)("ext_no").ToString
        //            Employee.Floor = ReturnDS.Tables("psn_open_data").Rows(Row)("floor").ToString
        //            Employee.Counter = ReturnDS.Tables("psn_open_data").Rows(Row)("counter").ToString
        //            Employee.Dept_Desc = ReturnDS.Tables("psn_open_data").Rows(Row)("dept_desc").ToString
        //            Employee.IsAutouth = CType(Employee.INEffective_date, Integer) > CType(SystemDate, Integer)
        //        End If
        //    Catch ex As Exception
        //        Employee.IsAutouth = False
        //    Finally
        //        ReturnDS.Dispose()
        //    End Try
        //    Return Employee
        //End Function

        //-----------------------------'
        //取得SERVER檔案路徑
        //傳入:funcID 程式代號--請參考data_interface的列表
        //20050506 add by walter
        //-----------------------------'

        public string getServerPath(string sFuncID)
        {
            string returnValue = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();

            DataBase.clearSB(sb);
            sb.Append("Select * From BAS_DATA_INTERFACE ");
            //Tony : 1.修改SQL語法使用參數，不組字串
            //sb.Append("Where interface_id = \'" + sFuncID + "\'");
            sb.Append("Where interface_id = :sFuncID");

            //Tony : 2.設定動態參數物件
            m_arrParam.Add(dba.GetOracleParam(":sFuncID", sFuncID));

            //Tony : 3.修改執行方法(新增傳入參數)
            //DataSet ds = DataBase.Query_CLASS(sb);
            DataSet ds = DataBase.Query_CLASS(sb, m_arrParam);

            if (!(ds == null) && ds.Tables[0].Rows[0]["ReturnCode"] != "100" && ds.Tables[0].Rows[0]["ReturnCode"] != "999")
            {
                returnValue = System.Convert.ToString(ds.Tables[1].Rows[0]["server_path"].ToString());
            }
            else
            {
                returnValue = "";
            }
            return returnValue;
        }

        //-----------------------------'
        //驗證秘密花園連入字串是否正確
        //傳入1:sURL 祕密花園網址
        //傳入1:sUserID 登入者id
        //20050510 add by walter
        //-----------------------------'
        public int AuthURL(string sURL)
        {
            string sURL1 = System.Configuration.ConfigurationSettings.AppSettings["URL1"];
            string sURL2 = System.Configuration.ConfigurationSettings.AppSettings["URL2"];
            string sURL3 = System.Configuration.ConfigurationSettings.AppSettings["URL3"];
            string sURL4 = System.Configuration.ConfigurationSettings.AppSettings["URL4"];
            //Dim sURLTEST As String = System.Configuration.ConfigurationSettings.AppSettings("URL_TEST")
            //Dim p_Request_URL As String = UCase(Request.ServerVariables("HTTP_REFERER"))
            if ((sURL.ToUpper()).IndexOf(sURL1.ToUpper()) + 1 > 0)
            {
                return 1;
            }
            else if ((sURL.ToUpper()).IndexOf(sURL2.ToUpper()) + 1 > 0)
            {
                return 2;
            }
            else if ((sURL.ToUpper()).IndexOf(sURL3.ToUpper()) + 1 > 0)
            {
                return 3;
            }
            else if ((sURL.ToUpper()).IndexOf(sURL4.ToUpper()) + 1 > 0)
            {
                return 4;
            }
            else
            {
                //Response.Redirect("login.aspx")
                return 0;
            }

        }
        //************************************************************************************
        //FOR WORK_FLOW START
        //************************************************************************************
        //Public Function ReciveData(ByVal p_ddscDt As ddscDataSet) As String
        //    Dim ds As DataSet = p_ddscDt.getDataSet
        //    Dim DataBase As New DataBase
        //    Dim TRANS_NO As String, REMARK As String, AUT As String, CARD_ID As String
        //    Dim msg As String, ResponseXML As String

        //    TRANS_NO = ds.Tables(0).Rows(1)("TRANS_NO").ToString.Trim
        //    If TRANS_NO = "" Then
        //        msg = "單據號碼不可為空白！！"
        //    Else
        //        REMARK = ds.Tables(0).Rows(1)("REMARK").ToString
        //        AUT = ds.Tables(0).Rows(1)("AUT").ToString  'S or R or A
        //        CARD_ID = ds.Tables(0).Rows(1)("CARD_ID").ToString

        //        msg = Submit_Next(TRANS_NO, REMARK, AUT, CARD_ID)

        //    End If

        //    ResponseXML = "<Response>" + _
        //                                            "<items>" + msg + "</items>" + _
        //                                     "</Response>"
        //    Return ResponseXML
        //End Function
        //Public Function Submit_Next(ByVal TRANS_NO As String, ByVal REMARK As String, ByVal AUT As String, ByVal CARD_ID As String, Optional ByVal STATUS As String = "", Optional ByVal TABLENAME As String = "", Optional ByVal ReturnSQL_STR As Boolean = False, Optional ByVal DEPT As String = "") As String
        //    'TRANS_NO：單據號碼
        //    'REMARK：送審、呈核、核決、駁回之備註
        //    'AUT：A-核決 R-駁回 S-呈核 or 送審
        //    'CARD_ID ：單據別
        //    'STATUS：當送審時請傳入2
        //    'TABLENAME：當送審時請傳入TableName for 讀取PATH_ID
        //    'ReturnSQL_STR：傳回語法不執行(TRUE)

        //    Dim sqlCommand() As String, sql() As String, sqlstr As String
        //    Dim SEQ_NO As String, PATH_ID As String
        //    Dim Arr_TABLE As Array, i As Int32
        //    Dim msg As String
        //    Dim OnUser As Employee
        //    OnUser = LoginUser()
        //    If DEPT = "" Then
        //        DEPT = OnUser.Department
        //    End If
        //    Try

        //        If STATUS <> "2" Then
        //            DataBase.clearSB(sb)
        //            '讀取單據資料
        //            sb.Append("select SEQ_NO,PATH_ID,CARD_ID,REMARK from AUT_WORK_FLOW_LOG where TRANS_NO = '" & TRANS_NO & "' and DEPARTMENT_NO_A='" & DEPT & "' and STATUS='W'")
        //            Dim ReturnDS As DataSet = DataBase.Query_CLASS(sb)
        //            If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //                SEQ_NO = ReturnDS.Tables(1).Rows(0)("SEQ_NO")
        //                PATH_ID = ReturnDS.Tables(1).Rows(0)("PATH_ID")
        //                If ReturnDS.Tables(1).Rows(0)("REMARK").tostring.Trim <> "" Then
        //                    REMARK = ReturnDS.Tables(1).Rows(0)("REMARK").tostring.Trim + " " + REMARK
        //                End If
        //            Else
        //                DataBase.clearSB(sb)
        //                sb.Append("select SEQ_NO,PATH_ID,CARD_ID,REMARK from AUT_WORK_FLOW_LOG where TRANS_NO = '" & TRANS_NO & "' and STATUS='W'")
        //                ReturnDS = DataBase.Query_CLASS(sb)
        //                If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //                    SEQ_NO = ReturnDS.Tables(1).Rows(0)("SEQ_NO")
        //                    PATH_ID = ReturnDS.Tables(1).Rows(0)("PATH_ID")
        //                    If ReturnDS.Tables(1).Rows(0)("REMARK").tostring.Trim <> "" Then
        //                        REMARK = ReturnDS.Tables(1).Rows(0)("REMARK").tostring.Trim + " " + REMARK
        //                    End If
        //                Else
        //                    msg = "單據號碼：" + TRANS_NO + "-此筆資料正在審核中或已被送審！TransNo=" + TRANS_NO + "TransSEQ=" + SEQ_NO
        //                End If
        //            End If
        //            '讀取單據目前狀態
        //            DataBase.clearSB(sb)
        //            sb.Append("select TRANS_STATUS from AUT_WORK_FLOW_PATH where PATH_ID = '" & PATH_ID & "' and SEQ_NO='" & SEQ_NO & "'")
        //            ReturnDS = DataBase.Query_CLASS(sb)
        //            If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //                STATUS = ReturnDS.Tables(1).Rows(0)("TRANS_STATUS")
        //            Else
        //                msg = "單據號碼：" + TRANS_NO + "-找不到單據目前狀態，請設定WORK_FLOW_PATH！無法執行"
        //            End If
        //        Else
        //            DataBase.clearSB(sb)
        //            sb.Append("select SEQ_NO,PATH_ID,CARD_ID from AUT_WORK_FLOW_LOG where TRANS_NO = '" & TRANS_NO & "' and (STATUS ='P' or STATUS = 'W')")
        //            'sb.Append("select SEQ_NO,PATH_ID,CARD_ID from WORK_FLOW_LOG where TRANS_NO = '" & TRANS_NO & "'")
        //            Dim ReturnDS As DataSet = DataBase.Query_CLASS(sb)
        //            If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //                msg = "單據號碼：" + TRANS_NO + "-此單據資料正在審核中！TransNo=" + TRANS_NO
        //                Return msg
        //            End If
        //            '於畫面按下審核button, 審核流程第一關必需抓WORK_FLOW_FORM之PATH_ID
        //            SEQ_NO = "10"
        //            '1)讀取PATH_ID
        //            DataBase.clearSB(sb)
        //            sb.Append("select PATH_ID from AUT_WORK_FLOW_FORM where TRANS_TABLE = '" & TABLENAME & "' and CARD_ID='" & CARD_ID & "' and Department_no='" & DEPT & "'")
        //            ReturnDS = DataBase.Query_CLASS(sb)
        //            If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //                PATH_ID = ReturnDS.Tables(1).Rows(0)("PATH_ID")
        //            Else
        //                msg = "單據號碼：" + TRANS_NO + "-WORK_FLOW_FROM設定不完整！"
        //            End If
        //        End If


        //        If msg = "" Then
        //            msg = WORK_FLOW(TRANS_NO, REMARK, AUT, CARD_ID, SEQ_NO, PATH_ID, STATUS, ReturnSQL_STR)
        //        End If
        //        'update P-->W
        //        sqlstr = "update AUT_WORK_FLOW_LOG set STATUS = 'W' where TRANS_NO = '" & TRANS_NO & "' and SEQ_NO='" & SEQ_NO & "' and STATUS='P'"
        //        DataBase.clearSB(sb)
        //        sb.Append(sqlstr)
        //        Try
        //            DataBase.Query_CLASS(sb)
        //        Catch exx As Exception
        //            msg = exx.Message
        //        End Try



        //    Catch ex As Exception
        //        'update P-->W
        //        sqlstr = "update AUT_WORK_FLOW_LOG set STATUS = 'W' where TRANS_NO = '" & TRANS_NO & "' and SEQ_NO='" & SEQ_NO & "' and STATUS='P'"
        //        DataBase.clearSB(sb)
        //        sb.Append(sqlstr)

        //        Try
        //            DataBase.Query_CLASS(sb)
        //        Catch exx As Exception
        //            msg = exx.Message
        //        End Try
        //        msg = ex.Message

        //    End Try
        //    Return msg
        //End Function
        //Public Function WORK_FLOW(ByVal TRANS_NO As String, ByVal REMARK As String, ByVal AUT As String, ByVal CARD_ID As String, ByVal SEQ_NO As String, ByVal PATH_ID As String, ByVal STATUS As String, Optional ByVal ReturnSQL_STR As Boolean = False) As String
        //    Dim WorkFlow As WorkFlow = New WorkFlow
        //    Dim sql() As String, sqlcommand() As String, sqlstr As String, msg As String
        //    Dim sql_str(0) As String
        //    Dim dba As New dbAccess
        //    Dim Arr_TABLE As Array
        //    Dim OnUser As Employee
        //    OnUser = LoginUser()
        //    Dim ReturnDS As DataSet
        //    Dim STATUS_A As String = ""
        //    Try
        //        '1)Update Work_FLOW_Log Status W--->P(防止重覆執行)
        //        sql = WorkFlow.updSTATUS(TRANS_NO, SEQ_NO, "P", REMARK, OnUser.ID)
        //        ReturnDS = dba.runExecuteNonQuery(sql(0))
        //        If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("TransResult") = "T") Then
        //            '2) Update Work_FLOW_Log Status W--->S，判斷是否有資料
        //            sql = WorkFlow.updSTATUS(TRANS_NO, SEQ_NO, AUT, REMARK, OnUser.ID)

        //            If sql(1) = "" Then
        //                sql_str(0) = sql(0)
        //                If AUT = "S" Then
        //                    '3) Insert Next Flow To Work_Flow_Los
        //                    sqlcommand = WorkFlow.InsertSTATUS(TRANS_NO, SEQ_NO, PATH_ID, OnUser.Department, OnUser.ID, CARD_ID, REMARK)

        //                    If sqlcommand(1) = "" Then

        //                        Dim CodeArr1 As Array, c As Int32
        //                        CodeArr1 = sqlcommand(0).Split(";")
        //                        If CodeArr1.GetUpperBound(0) = 0 Then
        //                            ReDim Preserve sql_str(sql_str.Length)
        //                            sql_str(sql_str.Length - 1) = sqlcommand(0)
        //                        Else
        //                            For c = 0 To CodeArr1.GetUpperBound(0) - 1
        //                                ReDim Preserve sql_str(sql_str.Length)
        //                                sql_str(sql_str.Length - 1) = CodeArr1(c)
        //                            Next
        //                        End If


        //                    Else
        //                        msg = sqlcommand(1)
        //                    End If
        //                End If
        //                '*************************************************************************************************
        //                'create 941027
        //                '*************************************************************************************************
        //                If AUT = "R" Then
        //                    '3) Insert Reject FLOW To Work_Flow_Los
        //                    sqlcommand = WorkFlow.InsertSTATUS_Reject(TRANS_NO, SEQ_NO, PATH_ID, OnUser.Department, OnUser.ID, CARD_ID, REMARK)

        //                    If sqlcommand(0) <> "" And sqlcommand(1) = "" Then

        //                        Dim CodeArr1 As Array, c As Int32
        //                        CodeArr1 = sqlcommand(0).Split(";")
        //                        If CodeArr1.GetUpperBound(0) = 0 Then
        //                            ReDim Preserve sql_str(sql_str.Length)
        //                            sql_str(sql_str.Length - 1) = sqlcommand(0)
        //                        Else
        //                            For c = 0 To CodeArr1.GetUpperBound(0) - 1
        //                                ReDim Preserve sql_str(sql_str.Length)
        //                                sql_str(sql_str.Length - 1) = CodeArr1(c)
        //                            Next
        //                        End If
        //                    Else
        //                        If sqlcommand(1) <> "" Then
        //                            msg = sqlcommand(1)
        //                        End If
        //                    End If
        //                    STATUS = sqlcommand(2)
        //                End If
        //                '*************************************************************************************************
        //                If msg = "" Then
        //                    If ReturnSQL_STR Then '不執行store procedure直接傳回語法
        //                        'update單據目前狀態
        //                        Dim sqlstr_a As String = ""
        //                        If AUT = "S" Then
        //                            STATUS_A = "W"
        //                        Else
        //                            STATUS_A = AUT
        //                        End If

        //                        sqlstr_a = ";UPDATE AUT_WORK_FLOW_LOG SET STATUS_A = '" & STATUS_A & "' WHERE TRANS_NO='" & TRANS_NO & "' and STATUS<>'P'"
        //                        Dim str_sql As String
        //                        If sql_str.Length >= 2 Then
        //                            Dim c As Int32
        //                            'Return sql_str(0) + ";" + sql_str(1)
        //                            For c = 0 To sql_str.Length - 1
        //                                If c = sql_str.Length - 1 Then
        //                                    str_sql = str_sql + sql_str(c)
        //                                Else
        //                                    str_sql = str_sql + sql_str(c) + "; "
        //                                End If

        //                            Next

        //                            str_sql = str_sql + sqlstr_a
        //                            Return str_sql
        //                        Else
        //                            str_sql = sql_str(0) + sqlstr_a
        //                            Return str_sql
        //                        End If
        //                    End If
        //                    '呼叫Store Procedure CARD_ID + SPNAME
        //                    sqlstr = "Begin " & CARD_ID & "_TRANS_DEAL('" + TRANS_NO + "','" + DateTime.Now().ToString("yyyyMMdd") + "','" + AUT + "','" + STATUS + "','" + OnUser.ID + "','" + OnUser.Department + "'); END;"
        //                    ReDim Preserve sql_str(sql_str.Length)
        //                    sql_str(sql_str.Length - 1) = sqlstr

        //                    '駁回傳送mail可該流程中所有人員
        //                    If AUT = "R" Then

        //                        DataBase.clearSB(sb)
        //                        sb.Append("BEGIN ")
        //                        sb.Append("SP_SEND_MAIL_FOR_FLOW('" + TRANS_NO + "','R','" + REMARK + "','" + CARD_ID + "');")
        //                        sb.Append("END;")
        //                        sqlstr = sb.ToString
        //                        ReDim Preserve sql_str(sql_str.Length)
        //                        sql_str(sql_str.Length - 1) = sqlstr

        //                    End If
        //                    'update單據目前狀態()
        //                    If AUT = "S" Then
        //                        STATUS_A = "W"
        //                    Else
        //                        STATUS_A = AUT
        //                    End If

        //                    sqlstr = "UPDATE AUT_WORK_FLOW_LOG SET STATUS_A = '" & STATUS_A & "' WHERE TRANS_NO='" & TRANS_NO & "' and STATUS<>'P' "
        //                    ReDim Preserve sql_str(sql_str.Length)
        //                    sql_str(sql_str.Length - 1) = sqlstr

        //                    ReturnDS = dba.runExecuteNonQuery(sql_str)
        //                    If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("TransResult") = "T") Then
        //                        msg = "單據號碼：" + TRANS_NO + "-執行成功"

        //                    Else
        //                        msg = "單據號碼：" + TRANS_NO + "-執行失敗 " + ReturnDS.Tables(0).Rows(0)("Message")
        //                    End If
        //                Else
        //                    '錯誤訊息
        //                    '回傳組合XML
        //                    msg = sqlcommand(1)
        //                End If
        //            End If
        //        Else
        //            '無法將W-->P
        //            msg = ReturnDS.Tables(0).Rows(0)("Message")
        //        End If
        //    Catch ex As Exception
        //        msg = ex.Message
        //    End Try
        //    ReturnDS.Dispose()
        //    Return msg
        //End Function
        //Public Function WORK_FLOW_TYPE_A(ByVal TRANS_NO As String, ByVal REMARK As String, ByVal AUT As String, ByVal CARD_ID As String, ByVal DEPT_ID As String, ByVal USER_ID As String) As String
        //    Dim DataBase As New DataBase
        //    Dim dba As New dbAccess
        //    Dim sql_str(0) As String
        //    Dim msg As String = "", SEQ_NO As String, updSTATUS_SQL As String, PATH_ID As String, TRANS_STATUS As String
        //    Try
        //        updSTATUS_SQL = "Update AUT_Work_FLOW_LOG set STATUS='" & AUT & "',REMARK='" & REMARK & "',EMPLOYEE_NO_A='" & USER_ID & "' ,DATE_A='" & DateTime.Now().ToString("yyyyMMdd") & "',TIME_A='" & DateTime.Now().ToString("HHmmss") & "'"
        //        updSTATUS_SQL = updSTATUS_SQL + "  where Trans_NO='" & TRANS_NO & "' AND STATUS = 'W'"
        //        sql_str(0) = updSTATUS_SQL

        //        '讀取序號
        //        sb.Append("select SEQ_NO,PATH_ID,CARD_ID,REMARK from AUT_WORK_FLOW_LOG where TRANS_NO = '" & TRANS_NO & "' and STATUS='P' Order by SEQ_NO")
        //        Dim ReturnDS As DataSet = DataBase.Query_CLASS(sb)
        //        If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //            SEQ_NO = ReturnDS.Tables(1).Rows(0)("SEQ_NO")
        //            PATH_ID = ReturnDS.Tables(1).Rows(0)("PATH_ID")
        //            If ReturnDS.Tables(1).Rows(0)("REMARK").ToString.Trim <> "" Then
        //                REMARK = ReturnDS.Tables(1).Rows(0)("REMARK").ToString.Trim + " " + REMARK
        //            End If
        //        End If
        //        If AUT = "S" And msg.Trim = "" Then
        //            updSTATUS_SQL = "Update AUT_Work_FLOW_LOG set STATUS='W',DEPARTMENT_NO_S='" & DEPT_ID & "',EMPLOYEE_NO_S='" & USER_ID & "' ,DATE_S='" & DateTime.Now().ToString("yyyyMMdd") & "',TIME_S='" & DateTime.Now().ToString("HHmmss") & "'"
        //            updSTATUS_SQL = updSTATUS_SQL + "  where Trans_NO='" & TRANS_NO & "' AND SEQ_no = '" & SEQ_NO & "' AND STATUS = 'P'"
        //            ReDim Preserve sql_str(sql_str.Length)
        //            sql_str(sql_str.Length - 1) = updSTATUS_SQL
        //        Else
        //            updSTATUS_SQL = "Delete AUT_Work_FLOW_LOG "
        //            updSTATUS_SQL = updSTATUS_SQL + "  where Trans_NO='" & TRANS_NO & "' AND STATUS = 'P'"
        //            ReDim Preserve sql_str(sql_str.Length)
        //            sql_str(sql_str.Length - 1) = updSTATUS_SQL
        //        End If
        //        '呼叫STORE PROCEDURE
        //        '讀取STATUS
        //        DataBase.clearSB(sb)
        //        sb.Append("select SEQ_NO,PATH_ID,CARD_ID,REMARK from AUT_WORK_FLOW_LOG where TRANS_NO = '" & TRANS_NO & "' and STATUS='W' Order by SEQ_NO")
        //        ReturnDS = DataBase.Query_CLASS(sb)
        //        If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //            SEQ_NO = ReturnDS.Tables(1).Rows(0)("SEQ_NO")
        //        End If
        //        DataBase.clearSB(sb)
        //        sb.Append("select TRANS_STATUS from AUT_WORK_FLOW_PATH where PATH_ID = '" & PATH_ID & "' and SEQ_NO='" & SEQ_NO & "'")
        //        ReturnDS = DataBase.Query_CLASS(sb)
        //        If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "100" AndAlso ReturnDS.Tables(0).Rows(0)("ReturnCode") <> "999") Then
        //            TRANS_STATUS = ReturnDS.Tables(1).Rows(0)("TRANS_STATUS")
        //        Else
        //            TRANS_STATUS = "2"
        //        End If
        //        updSTATUS_SQL = "Begin A_TRANS_DEAL('" + TRANS_NO + "','" + DateTime.Now().ToString("yyyyMMdd") + "','" + AUT + "','" + TRANS_STATUS + "','" + USER_ID + "','" + DEPT_ID + "'); END;"
        //        ReDim Preserve sql_str(sql_str.Length)
        //        sql_str(sql_str.Length - 1) = updSTATUS_SQL


        //        '駁回傳送mail可該流程中所有人員
        //        Dim sqlstr As String = ""
        //        If AUT = "R" Then

        //            DataBase.clearSB(sb)
        //            sb.Append("BEGIN ")
        //            sb.Append("SP_SEND_MAIL_FOR_FLOW('" + TRANS_NO + "','R','" + REMARK + "','" + CARD_ID + "');")
        //            sb.Append("END;")
        //            sqlstr = sb.ToString
        //            ReDim Preserve sql_str(sql_str.Length)
        //            sql_str(sql_str.Length - 1) = sqlstr

        //        End If

        //        'update單據目前狀態
        //        Dim STATUS_A As String = ""
        //        If AUT = "S" Then
        //            STATUS_A = "W"
        //        Else
        //            STATUS_A = AUT
        //        End If

        //        sqlstr = "UPDATE AUT_WORK_FLOW_LOG SET STATUS_A = '" & STATUS_A & "' WHERE TRANS_NO='" & TRANS_NO & "' and STATUS<>'P' "
        //        ReDim Preserve sql_str(sql_str.Length)
        //        sql_str(sql_str.Length - 1) = sqlstr

        //        ReturnDS = dba.runExecuteNonQuery(sql_str)
        //        If (Not ReturnDS Is Nothing AndAlso ReturnDS.Tables(0).Rows(0)("TransResult") = "T") Then
        //            msg = "單據號碼：" + TRANS_NO + "-執行成功"
        //        Else
        //            msg = "單據號碼：" + TRANS_NO + "-執行失敗 " + ReturnDS.Tables(0).Rows(0)("Message")
        //        End If
        //    Catch ex As Exception
        //        msg = ex.Message
        //    End Try
        //    Return msg
        //End Function
        public string ROLL_BACK_P_TO_W(string TRANS_NO)
        {
            string msg = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {
                //update P-->W
                //Tony : 1.修改SQL語法使用參數，不組字串
                //string sqlstr = "update AUT_WORK_FLOW_LOG set STATUS = \'W\',REMARK=\'\' where TRANS_NO = \'" + TRANS_NO + "\' and STATUS=\'P\'";
                string sqlstr = "update AUT_WORK_FLOW_LOG set STATUS = \'W\',REMARK=\'\' where TRANS_NO = :TRANS_NO and STATUS=\'P\'";

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":TRANS_NO", TRANS_NO));

                DataBase.clearSB(sb);
                sb.Append(sqlstr);

                //Tony : 3.修改執行方法(新增傳入參數)
                //DataBase.Query_CLASS(sb);
                //DataBase.Query_CLASS(sb,m_arrParam);
                dba.runExecuteNonQuery(sqlstr, m_arrParam);

                msg = "0"; //成功
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }
        //************************************************************************************
        //FOR WORK_FLOW END
        //************************************************************************************


        //寫出ftp要用的劇本檔 pKEY update by titan
        public void FTP_Process(string pS_Path, string pCDPath, string[] aryFiles, string pKEY = "FTP_DATA")
        {
            string pS_Msg;
            string pS_Detail = "";
            string pS_OpenFileName = "";
            long pID;
            long PHnd;
            long Ret;
            StreamWriter dataSW = default(StreamWriter);
            int i = 0;

            string sData = getFTPData(pKEY);
            string[] sFtpData = sData.Split(',');

            //callFTP(pS_Path, pCDPath, aryFiles, sFtpData(0), sFtpData(1), sFtpData(2))

            pS_OpenFileName = pS_Path + "FTP";
            dataSW = new StreamWriter(pS_OpenFileName, false);
            pS_Detail = "open " + sFtpData[0]; //FTP IP ADDRESS
            dataSW.WriteLine(pS_Detail);
            pS_Detail = sFtpData[1]; //*--* Test USER_ID
            dataSW.WriteLine(pS_Detail); //pS_Detail = "FTP"
            pS_Detail = sFtpData[2]; //*--* Test PASSWORD
            dataSW.WriteLine(pS_Detail);
            pS_Detail = "quote type b 8";
            dataSW.WriteLine(pS_Detail);
            pS_Detail = pCDPath;
            dataSW.WriteLine(pS_Detail);
            for (i = 0; i <= aryFiles.Length - 1; i++)
            {
                pS_Detail = "put " + aryFiles[i];
                dataSW.WriteLine(pS_Detail);
            }
            pS_Detail = "quit";
            dataSW.WriteLine(pS_Detail);
            dataSW.Close();

            callFTP(pS_OpenFileName);
            //pID = Shell("ftp -s:" & pS_OpenFileName)
            //PHnd = OpenProcess(SYNCHRONIZE, 0, pID)
            //If PHnd <> 0 Then
            //    Do
            //        Ret = WaitForSingleObject(PHnd, 0)
            //    Loop While Ret = WAIT_TIMEOUT

            //    CloseHandle(PHnd)
            //End If


        }
        //FTP 公用程式
        public void callFTP(string pS_OpenFileName)
        {
            long pID = 0;
            long PHnd = 0;
            long Ret = 0;

            pID = Interaction.Shell("ftp -s:" + pS_OpenFileName, AppWinStyle.MinimizedFocus, false, -1);
            PHnd = System.Convert.ToInt64(OpenProcess(SYNCHRONIZE, 0, pID));
            if (PHnd != 0)
            {
                do
                {
                    Ret = System.Convert.ToInt64(WaitForSingleObject(PHnd, 0));
                } while (Ret == WAIT_TIMEOUT);

                CloseHandle(PHnd);
            }
        }


        public string getFTPData(string sFuncID)
        {
            string returnValue = "";
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();

            DataBase.clearSB(sb);
            sb.Append("Select * From BAS_DATA_INTERFACE ");

            //Tony : 1.修改SQL語法使用參數，不組字串
            sb.Append("Where interface_id = :sFuncID");

            //Tony : 2.設定動態參數物件
            m_arrParam.Add(dba.GetOracleParam(":sFuncID", sFuncID));

            string sID = "";
            string sPWD = "";
            string sIP = "";

            //Tony : 3.修改執行方法(新增傳入參數)
            //DataSet ds = DataBase.Query_CLASS(sb);
            DataSet ds = DataBase.Query_CLASS(sb, m_arrParam);

            if (!(ds == null) && ds.Tables[0].Rows[0]["ReturnCode"] != "100" && ds.Tables[0].Rows[0]["ReturnCode"] != "999")
            {
                sIP = System.Convert.ToString(ds.Tables[1].Rows[0]["server_path"].ToString());
                sID = System.Convert.ToString(ds.Tables[1].Rows[0]["user_id"].ToString());
                sPWD = System.Convert.ToString(ds.Tables[1].Rows[0]["password"].ToString());
                returnValue = sIP + "," + sID + "," + sPWD;
            }
            else
            {
                //若沒值,給預設的測試環境
                returnValue = "10.3.23.185,finuser,123456";
                //getFTPData = "10.3.26.181,FTP_POSUSER,FTP_POSUSER"
            }
            return returnValue;
        }


        public DataSet Delete_TEMPFILE(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string SEQ = "";
            string TABLENAME = "";
            string sqlstr = "";
            DataSet dsReturn = default(DataSet);
            DataBase DataBase = new DataBase();
            StringBuilder sb = new StringBuilder();
            string[] sql_str = new string[1];
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();
            try
            {
                // 查詢資料總筆數之 sql command
                SEQ = ds.Tables[0].Rows[1]["SEQ"].ToString();
                TABLENAME = ds.Tables[0].Rows[1]["TABLENAME"].ToString();

                //Tony : 1.修改SQL語法使用參數，不組字串
                //sqlstr = "Begin SP_DELETE_REPORT_TEMPFILE(\'" + SEQ + "\',\'" + TABLENAME + "\'); END;";
                sqlstr = "Begin SP_DELETE_REPORT_TEMPFILE(:SEQ,:TABLENAME); END;";

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":SEQ", SEQ));
                m_arrParam.Add(dba.GetOracleParam(":TABLENAME", TABLENAME));

                sql_str[0] = sqlstr;

                //Tony : 3.修改執行方法Overload
                //dsReturn = dba.runExecuteNonQuery(sql_str);
                dsReturn = dba.runExecuteNonQuery(sql_str.ToString(), m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }
        //Public Function SEQ(ByVal p_ddscDt As ddscDataSet) As DataSet
        //    Dim ds As DataSet = p_ddscDt.getDataSet
        //    Dim STORE As String
        //    Dim dsReturn As DataSet
        //    Dim DataBase As New DataBase
        //    Try
        //        ' 查詢資料總筆數之 sql command
        //        Dim sb As StringBuilder = New StringBuilder
        //        sb.Append("select reportservice_seq.nextval as SEQ from dual")
        //        dsReturn = DataBase.Query_CLASS(sb)

        //    Catch ex As Exception
        //        Throw ex
        //    End Try

        //    Return dsReturn
        //End Function

        //查詢庫存量相關資料
        public DataSet Query_INVENTORY(com.ddsc.common.ddscDataSet p_ddscDt)
        {

            DataSet ds = p_ddscDt.getDataSet();
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();
            try
            {
                // SELECT (A.curr_qty-A.pren_qty-A.store_onway_qty+A.po_onway_qty) AS qty
                //FROM inventory a
                //WHERE A.store_no='KIX' AND A.code='1300036'

                string STORE_NO = System.Convert.ToString(ds.Tables[0].Rows[1]["STORE_NO"]);
                string CODE = System.Convert.ToString(ds.Tables[0].Rows[1]["CODE"]);

                sb.Append("SELECT (A.curr_qty-A.pren_qty-A.h_onway_qty+A.po_onway_qty) AS QTY ");
                sb.Append(" FROM INV_INVENTORY a ");
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append("Where CODE = \'" + CODE + "\'");
                //sb.Append(" And STORE_NO = \'" + STORE_NO + "\'");
                sb.Append("Where CODE = :CODE");
                sb.Append(" And STORE_NO = :STORE_NO");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":CODE", CODE));
                m_arrParam.Add(dba.GetOracleParam(":STORE_NO", STORE_NO));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb.ToString(), "INVENTORY");
                dsReturn = DataBase.Query_CLASS(sb.ToString(), "INVENTORY", m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        //寫出ftp要用的劇本檔
        public void FTP_Process1(string pS_Path, string pCDPath, string[] aryFiles)
        {
            string pS_Msg;
            string pS_Detail = "";
            string pS_OpenFileName = "";
            long pID;
            long PHnd;
            long Ret;
            StreamWriter dataSW = default(StreamWriter);
            int i = 0;

            string sData = getFTPData("FTP_DATA1");
            string[] sFtpData = sData.Split(',');

            //callFTP(pS_Path, pCDPath, aryFiles, sFtpData(0), sFtpData(1), sFtpData(2))

            pS_OpenFileName = pS_Path + "FTP";
            dataSW = new StreamWriter(pS_OpenFileName, false);
            pS_Detail = "open " + sFtpData[0]; //FTP IP ADDRESS
            dataSW.WriteLine(pS_Detail);
            pS_Detail = sFtpData[1]; //*--* Test USER_ID
            dataSW.WriteLine(pS_Detail); //pS_Detail = "FTP"
            pS_Detail = sFtpData[2]; //*--* Test PASSWORD
            dataSW.WriteLine(pS_Detail);
            pS_Detail = "quote type b 8";
            dataSW.WriteLine(pS_Detail);
            pS_Detail = pCDPath;
            dataSW.WriteLine(pS_Detail);
            for (i = 0; i <= aryFiles.Length - 1; i++)
            {
                pS_Detail = "get " + aryFiles[i] + " " + pS_Path + aryFiles[i];
                dataSW.WriteLine(pS_Detail);
            }
            pS_Detail = "quit";
            dataSW.WriteLine(pS_Detail);
            dataSW.Close();

            callFTP(pS_OpenFileName);


        }

        //寫出ftp要用的劇本檔
        public void FTP_Process2(string pS_Path, string pCDPath, string[] aryFiles)
        {
            string pS_Msg;
            string pS_Detail = "";
            string pS_OpenFileName = "";
            long pID;
            long PHnd;
            long Ret;
            StreamWriter dataSW = default(StreamWriter);
            int i = 0;

            string sData = getFTPData("FTP_DATA1");
            string[] sFtpData = sData.Split(',');

            //callFTP(pS_Path, pCDPath, aryFiles, sFtpData(0), sFtpData(1), sFtpData(2))

            pS_OpenFileName = pS_Path + "FTP";
            dataSW = new StreamWriter(pS_OpenFileName, false);
            pS_Detail = "open " + sFtpData[0]; //FTP IP ADDRESS
            dataSW.WriteLine(pS_Detail);
            pS_Detail = sFtpData[1]; //*--* Test USER_ID
            dataSW.WriteLine(pS_Detail); //pS_Detail = "FTP"
            pS_Detail = sFtpData[2]; //*--* Test PASSWORD
            dataSW.WriteLine(pS_Detail);
            pS_Detail = "quote type b 8";
            dataSW.WriteLine(pS_Detail);
            pS_Detail = pCDPath;
            dataSW.WriteLine(pS_Detail);
            for (i = 0; i <= aryFiles.Length - 1; i++)
            {
                pS_Detail = "put " + pS_Path + aryFiles[i] + " " + aryFiles[i];
                dataSW.WriteLine(pS_Detail);
            }
            pS_Detail = "quit";
            dataSW.WriteLine(pS_Detail);
            dataSW.Close();

            callFTP(pS_OpenFileName);

        }

        //寫出ftp要用的劇本檔
        public void FTP_Process11(string pS_Path, string pCDPath, string[] aryFiles, string IP_ADDRESS)
        {
            string pS_Msg;
            string pS_Detail = "";
            string pS_OpenFileName = "";
            long pID;
            long PHnd;
            long Ret;
            StreamWriter dataSW = default(StreamWriter);
            int i = 0;

            string sData = getFTPData("FTP_DATA1");
            string[] sFtpData = sData.Split(',');

            //callFTP(pS_Path, pCDPath, aryFiles, sFtpData(0), sFtpData(1), sFtpData(2))

            pS_OpenFileName = pS_Path + "FTP";
            dataSW = new StreamWriter(pS_OpenFileName, false);
            pS_Detail = "open " + sFtpData[0]; //FTP IP ADDRESS
            dataSW.WriteLine(pS_Detail);
            pS_Detail = sFtpData[1]; //*--* Test USER_ID
            dataSW.WriteLine(pS_Detail); //pS_Detail = "FTP"
            pS_Detail = sFtpData[2]; //*--* Test PASSWORD
            dataSW.WriteLine(pS_Detail);
            pS_Detail = "quote type b 8";
            dataSW.WriteLine(pS_Detail);
            pS_Detail = pCDPath;
            dataSW.WriteLine(pS_Detail);

            pS_Detail = "mkdir " + IP_ADDRESS;
            dataSW.WriteLine(pS_Detail);

            pS_Detail = "cd " + IP_ADDRESS;
            dataSW.WriteLine(pS_Detail);

            //pS_Detail = "mdelete * "
            //dataSW.WriteLine(pS_Detail)

            for (i = 0; i <= aryFiles.Length - 1; i++)
            {
                pS_Detail = "delete " + aryFiles[i];
                dataSW.WriteLine(pS_Detail);

                pS_Detail = "put " + pS_Path + aryFiles[i] + " " + aryFiles[i];
                dataSW.WriteLine(pS_Detail);
            }
            pS_Detail = "quit";
            dataSW.WriteLine(pS_Detail);
            dataSW.Close();

            callFTP(pS_OpenFileName);

        }

        //帶出主類別
        public DataSet Query_BAS_MATERIAL_CLASS()
        {
            DataSet dsReturn = default(DataSet);

            try
            {

                // 查詢資料總筆數之 sql command

                StringBuilder sb = new StringBuilder();
                sb.Append("Select class,(class || \'-\' || class_name) class_name From BAS_MATERIAL_CLASS ");
                sb.Append("Where class_level = \'1\' ORDER BY class");

                // DataTable Name
                string dtName = "BAS_MATERIAL_CLASS";

                // Sql command
                string sqlCommands = sb.ToString();

                // create database access object
                dbAccess dba = new dbAccess();
                dsReturn = dba.runExecuteQuery(dtName, sqlCommands);

            }
            catch (Exception)
            {
                // create return object (DataSet)
                dsReturn = new DataSet("Query_CLASS");
            }

            return dsReturn;
        }

        //帶出次類別
        public DataSet Query_BAS_MATERIAL_SUB_CLASS(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet dsReturn = default(DataSet);
            DataSet ds = p_ddscDt.getDataSet();
            string str_class = "";
            ArrayList m_arrParam = new ArrayList();
            // create database access object
            dbAccess dba = new dbAccess();

            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                str_class = ds.Tables[0].Rows[1]["class"].ToString();
                sb.Append("Select subclass,(subclass || \'-\' || subclass_name) subclass_name From BAS_MATERIAL_CLASS ");
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append("Where class_level=\'2\' and class = \'" + str_class + "\' order by subclass ");
                sb.Append("Where class_level=\'2\' and class = :str_class order by subclass ");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":str_class", str_class));

                // DataTable Name
                string dtName = "BAS_MATERIAL_CLASS";

                // Sql command
                string sqlCommands = sb.ToString();


                //Tony : 3.修改執行方法Overload
                //dsReturn = dba.runExecuteQuery(dtName, sqlCommands);
                dsReturn = dba.runExecuteQuery(dtName, sqlCommands, m_arrParam);

            }
            catch (Exception)
            {
                // create return object (DataSet)
                dsReturn = new DataSet("BAS_MATERIAL_CLASS");

            }

            return dsReturn;
        }


        public DataSet Query_STORE(com.ddsc.common.ddscDataSet p_ddscDt)
        {
            DataSet ds = p_ddscDt.getDataSet();
            string STORE_NO = "";
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();

            try
            {
                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();
                STORE_NO = ds.Tables[0].Rows[1]["STORE_NO"].ToString().Trim();
                sb.Append("Select * From STORE ");
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where STORE_NO = \'" + STORE_NO + "\'");
                sb.Append(" Where STORE_NO = :STORE_NO");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":STORE_NO", STORE_NO));

                //Tony : 3.修改執行方法(新增傳入參數)
                //dsReturn = DataBase.Query_CLASS(sb);
                dsReturn = DataBase.Query_CLASS(sb, m_arrParam);

            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsReturn;
        }

        //查詢匯率
        public string Query_RATE(string LSTR_EXCH_TYPE, string LSTR_EXCH_DATE, string LSTR_CUR_DIVIDEND, string LSTR_CUR_DIVISOR)
        {
            DataSet ds = default(DataSet);
            double LDOU_EXCH_RATE = 0;
            ArrayList m_arrParam = new ArrayList();
            dbAccess dba = new dbAccess();
            try
            {

                // 查詢資料總筆數之 sql command
                StringBuilder sb = new StringBuilder();

                sb.Append("Select EX_RATE From CRW_EXCHANGE_RATE");
                //Tony : 1.修改SQL語法使用參數，不組字串
                //sb.Append(" Where EXCH_TYPE=\'" + LSTR_EXCH_TYPE + "\' AND EXCH_DATE = \'" + LSTR_EXCH_DATE + "\' AND CUR_DIVIDEND=\'" + LSTR_CUR_DIVIDEND + "\' AND CUR_DIVISOR=\'" + LSTR_CUR_DIVISOR + "\'");
                sb.Append(" Where EXCH_TYPE=\'" + LSTR_EXCH_TYPE + "\' AND EXCH_DATE = \'" + LSTR_EXCH_DATE + "\' AND CUR_DIVIDEND=\'" + LSTR_CUR_DIVIDEND + "\' AND CUR_DIVISOR=\'" + LSTR_CUR_DIVISOR + "\'");

                sb.Append(" And Status<> \'0\'");

                //Tony : 2.設定動態參數物件
                m_arrParam.Add(dba.GetOracleParam(":LSTR_EXCH_TYPE", LSTR_EXCH_TYPE));
                m_arrParam.Add(dba.GetOracleParam(":LSTR_EXCH_DATE", LSTR_EXCH_DATE));
                m_arrParam.Add(dba.GetOracleParam(":LSTR_CUR_DIVIDEND", LSTR_CUR_DIVIDEND));
                m_arrParam.Add(dba.GetOracleParam(":LSTR_CUR_DIVISOR", LSTR_CUR_DIVISOR));

                //Tony : 3.修改執行方法(新增傳入參數)
                //ds = DataBase.Query_CLASS(sb);
                ds = DataBase.Query_CLASS(sb, m_arrParam);

                if (!(ds == null) && ds.Tables[0].Rows[0]["ReturnCode"] != "100" && ds.Tables[0].Rows[0]["ReturnCode"] != "999")
                {
                    LDOU_EXCH_RATE = System.Convert.ToDouble(ds.Tables[1].Rows[0]["EX_RATE"].ToString());
                }
                else
                {
                    LDOU_EXCH_RATE = 0;
                }

            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }

            return System.Convert.ToString(LDOU_EXCH_RATE);
        }
        //Public Function DateAdd(ByVal p_ddscDt As ddscDataSet) As String
        //    Dim ds As DataSet = p_ddscDt.getDataSet
        //    Dim ResponseXML As String
        //    Dim DataBase As New DataBase
        //    Dim dba As New dbAccess
        //    Dim sql_str(0) As String

        //    Dim strInterval As String = ds.Tables(0).Rows(1)("strInterval").ToString
        //    Dim NumDay As Int32 = Convert.ToInt32(ds.Tables(0).Rows(1)("NumDay").ToString)
        //    Dim dtDate_s As String = ds.Tables(0).Rows(1)("dtDate").ToString
        //    Dim dtDate As Date = Convert.ToDateTime(Left(dtDate_s, 4) + "/" + Mid(dtDate_s, 5, 2) + "/" + Right(dtDate_s, 2))
        //    Dim returndate As Date
        //    Select Case strInterval
        //        Case "S"
        //            returndate = dtDate.AddSeconds(NumDay)
        //        Case "N"
        //            returndate = dtDate.AddMinutes(NumDay)
        //        Case "H"
        //            returndate = dtDate.AddHours(NumDay)
        //        Case "D"
        //            returndate = dtDate.AddDays(NumDay)
        //        Case "W"
        //            returndate = dtDate.AddDays(NumDay * 7)
        //        Case "M"
        //            returndate = dtDate.AddMonths(NumDay)
        //        Case "Y"
        //            returndate = dtDate.AddYears(NumDay)
        //    End Select
        //    returndate.ToString("yyyyMMdd")

        //    ResponseXML = "<Response>" + _
        //                                            "<items>" + returndate.ToString("yyyyMMdd") + "</items>" + _
        //                                     "</Response>"
        //    Return ResponseXML
        //End Function

        public string GET_Employee_NAME(string LSTR_Employee)
        {
            // 查詢資料內容
            string LSTR_CHINESE_NAME = "";
            StringBuilder sb = new StringBuilder();
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();

            //Tony : 1.修改SQL語法使用參數，不組字串
            //sb.Append("select CHINESE_NAME from psn_open_data where EMPLOYEE_NO=\'" + LSTR_Employee + "\'");
            sb.Append("select CHINESE_NAME from psn_open_data where EMPLOYEE_NO=:LSTR_Employee");

            //Tony : 2.設定動態參數物件
            m_arrParam.Add(dba.GetOracleParam(":LSTR_Employee", LSTR_Employee));

            //Tony : 3.修改執行方法(新增傳入參數)
            //DataSet ds1 = DataBase.Query_CLASS(sb);
            DataSet ds1 = DataBase.Query_CLASS(sb, m_arrParam);

            if (!(ds1 == null) && ds1.Tables[0].Rows[0]["ReturnCode"] != "100" && ds1.Tables[0].Rows[0]["ReturnCode"] != "999")
            {
                LSTR_CHINESE_NAME = System.Convert.ToString(ds1.Tables[1].Rows[0]["CHINESE_NAME"].ToString());
            }

            return LSTR_CHINESE_NAME;

        }

        public string GET_CURRENT_STND_LOAD_DATA(string LSTR_DEPT_DATE)
        {
            // 查詢資料內容
            string LSTR_STND_LOAD_DATA = "";
            string LSTR_STND_LOAD_NO = "";
            string LSTR_STND_LOAD_AREA = "";
            StringBuilder sb = new StringBuilder();
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();

            //Tony : 1.修改SQL語法使用參數，不組字串
            //sb.Append("SELECT STND_LOAD_NO,STND_LOAD_AREA FROM CRW_STANDARD_LOAD WHERE EFF_DATE <= \'" + LSTR_DEPT_DATE + "\' AND EXP_DATE >= \'" + LSTR_DEPT_DATE + "\' GROUP BY STND_LOAD_NO,STND_LOAD_AREA");
            sb.Append("SELECT STND_LOAD_NO,STND_LOAD_AREA FROM CRW_STANDARD_LOAD WHERE EFF_DATE <= :LSTR_DEPT_DATE AND EXP_DATE >= :LSTR_DEPT_DATE GROUP BY STND_LOAD_NO,STND_LOAD_AREA");

            //Tony : 2.設定動態參數物件
            m_arrParam.Add(dba.GetOracleParam(":LSTR_DEPT_DATE", LSTR_DEPT_DATE));

            //Tony : 3.修改執行方法(新增傳入參數)
            //DataSet ds = DataBase.Query_CLASS(sb);
            DataSet ds = DataBase.Query_CLASS(sb, m_arrParam);

            if (!(ds == null) && ds.Tables[0].Rows[0]["ReturnCode"] != "100" && ds.Tables[0].Rows[0]["ReturnCode"] != "999")
            {
                LSTR_STND_LOAD_NO = System.Convert.ToString(ds.Tables[1].Rows[0]["STND_LOAD_NO"].ToString());
                LSTR_STND_LOAD_AREA = System.Convert.ToString(ds.Tables[1].Rows[0]["STND_LOAD_AREA"].ToString());
                if (LSTR_STND_LOAD_NO != "" && LSTR_STND_LOAD_AREA != "")
                {
                    LSTR_STND_LOAD_DATA = LSTR_STND_LOAD_NO + "," + LSTR_STND_LOAD_AREA;
                }
            }

            return LSTR_STND_LOAD_DATA;

        }
        //creator By Titan
        //取得單據預設格式
        public dynamic GetDefaultTransactionNo(string CardID, int TransactionNoLength = 10)
        {
            int i = 0;
            string TransitionNo = CardID;
            string yy = DateTime.Today.ToString("yy");
            TransitionNo += yy;
            for (i = 0; i <= TransactionNoLength - CardID.Length - 3; i++)
            {
                TransitionNo += "X";
            }
            return TransitionNo;
        }
        //creator By Titan
        //取得字串亂數產生 ，傳入字串大小
        public dynamic GetRndString(int max)
        {
            int i;
            string RndString = "";
            //Dim A As String() = {"", "A0", "A1", "A2"}
            //Dim A0 As String() = {"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}
            //Dim A1 As String() = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
            //Dim A2 As String() = {"1", "2", "3", "4", "5", "6", "7", "8", "9", "0"}
            try
            {
                //For i = 0 To max
                //    Select Case A.GetValue(CInt(Int(((A.Length - 1) * Rnd()) + 1)))
                //        Case "A0"
                //            RndString += A0.GetValue(CInt(Int(((A0.Length - 1) * Rnd()) + 1)))
                //        Case "A1"
                //            RndString += A1.GetValue(CInt(Int(((A1.Length - 1) * Rnd()) + 1)))
                //        Case "A2"
                //            RndString += A2.GetValue(CInt(Int(((A2.Length - 1) * Rnd()) + 1)))
                //    End Select
                //Next
                return Guid.NewGuid().ToString().Split('-')[0].ToUpper();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #region 判斷LCS是否有傳送後待處理的訊息
        //回傳未處理的訊息--for LCS
        public DataSet GetMsgNoProcess()
        {
            dbAccess dba = new dbAccess();
            DataRow dr;
            DataSet ds = default(DataSet);
            string sqlStmt = "";

            try
            {
                sqlStmt = "select * from lcs_message_log where message_from = \'C\' and mark <> \'Y\' and mark <> \'*\' ";
                ds = dba.runExecuteQuery("WH_MESSAGE_LOG", sqlStmt);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {

            }
            return ds;
        }

        //檢查棧板是否存在傳送但未處理完畢的狀態，若有找到訊息，則回傳true，沒有找到則回傳false
        public bool CheckMsgIsExist(DataSet ds, string pallet_id, string message_type = "")
        {
            DataRow[] drs = null;
            bool flag = false;
            try
            {
                drs = ds.Tables["WH_MESSAGE_LOG"].Select("pallet_id = \'" + pallet_id + "\'");
                if (drs.Length > 0)
                {
                    flag = true;
                }
            }
            catch (Exception)
            {

            }
            return flag;

        }

        //檢查進倉時傳送訊息的時機
        //1.trans_tail_with_pallet裡Pallet_id的B單皆已完成驗貨，status >=4
        public bool CheckSendEP1Message(string pallet_id)
        {
            string sql = "";
            dbAccess dba = new dbAccess();
            DataSet ds = default(DataSet);
            bool result = true;
            ArrayList m_arrParam = new ArrayList();

            //Tony : 1.修改SQL語法使用參數，不組字串
            //sql = "select pallet_id from inv_trans_details_with_pallet where pallet_id = \'" + pallet_id + "\' and status < 4  and trans_no like \'B%\' ";
            sql = "select pallet_id from inv_trans_details_with_pallet where pallet_id = :pallet_id and status < 4  and trans_no like \'B%\' ";

            //Tony : 2.設定動態參數物件
            m_arrParam.Add(dba.GetOracleParam(":pallet_id", pallet_id));

            //Tony : 3.修改執行方法overload
            //ds = dba.runExecuteQuery("TRANS_TAIL_WITH_PALLET", sql);
            ds = dba.runExecuteQuery("TRANS_TAIL_WITH_PALLET", sql, m_arrParam);

            if (ds.Tables["info"].Rows[0]["ReturnCode"] == "000")
            {
                result = false;
            }
            return result;
        }

        //將等待傳送進倉的訊息更改為Z以準備進倉
        public void UpdateSendEP1Message(string pallet_id)
        {
            string sql = "";
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();

            //Tony : 1.修改SQL語法使用參數，不組字串
            //sql = "update lcs_message_log set mark = \'Z\' where pallet_id = \'" + pallet_id + "\' and mark = \'*\'";
            sql = "update lcs_message_log set mark = \'Z\' where pallet_id = :pallet_id and mark = \'*\'";

            //Tony : 2.設定動態參數物件
            m_arrParam.Add(dba.GetOracleParam(":pallet_id", pallet_id));

            //Tony : 3.修改執行方法overload
            //DataSet ds = dba.runExecuteNonQuery(sql);
            DataSet ds = dba.runExecuteNonQuery(sql, m_arrParam);
            ds.Dispose();

        }

        #endregion

        public string getB2BSerialNo(string SERIAL_TYPE, string SERIAL_CLASS, string SERIAL_YEAR, int SERIAL_LENGTH)
        {
            string result = string.Empty;
            DataSet ds = new DataSet();
            string serialNo = "";
            dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();

            //Tony : 1.修改SQL語法使用參數，不組字串
            //string sqlStmt = "select SERIAL_NO from B2B_SERIAL where SERIAL_TYPE = \'" + SERIAL_TYPE + "\' and SERIAL_CLASS = \'" + SERIAL_CLASS + "\' and SERIAL_YEAR = \'" + SERIAL_YEAR + "\'";
            string sqlStmt = "select SERIAL_NO from B2B_SERIAL where SERIAL_TYPE = :SERIAL_TYPE and SERIAL_CLASS = :SERIAL_CLASS and SERIAL_YEAR = :SERIAL_YEAR";

            //Tony : 2.設定動態參數物件
            m_arrParam.Add(dba.GetOracleParam(":SERIAL_TYPE", SERIAL_TYPE));
            m_arrParam.Add(dba.GetOracleParam(":SERIAL_CLASS", SERIAL_CLASS));
            m_arrParam.Add(dba.GetOracleParam(":SERIAL_YEAR", SERIAL_YEAR));

            dbConnection dbcon = new dbConnection();
            com.ddsc.common.ddscDataSet ddscDs = new com.ddsc.common.ddscDataSet();
            com.ddsc.common.ddscDataTable ddscDt = default(com.ddsc.common.ddscDataTable);
            SQLConverter sqlConvert = new SQLConverter();
            DataSet dsReturn = default(DataSet);

            try
            {
                //Tony : 3.修改執行方法overload
                //ds = dba.runExecuteQuery("B2B_SERIAL", sqlStmt);
                ds = dba.runExecuteQuery("B2B_SERIAL", sqlStmt, m_arrParam);

                //設定要Insert或Update的資料
                ddscDt = ddscDs.createDataTable("B2B_SERIAL");
                ddscDt.addColumn("action", "string");
                ddscDt.addColumn("SERIAL_TYPE", "string");
                ddscDt.addColumn("SERIAL_CLASS", "string");
                ddscDt.addColumn("SERIAL_YEAR", "string");
                ddscDt.addColumn("SERIAL_NO", "string");
                ddscDt.addColumn("upd_id", "string");
                ddscDt.addColumn("upd_date", "string");
                ddscDt.addColumn("upd_time", "string");
                //如果該年度尚未有流水序號，則由１開始並新增一筆至TRANS_SEQ
                if (ds.Tables["B2B_SERIAL"].Rows.Count == 0)
                {
                    serialNo = "1";
                    ddscDt.addColumn("crt_id", "string");
                    ddscDt.addColumn("crt_date", "string");
                    ddscDt.addColumn("crt_time", "string");
                    //ddscDt.addNewRow()
                    ddscDt.setColumnValue("action", "add");
                    ddscDt.setColumnValue("SERIAL_TYPE", SERIAL_TYPE.ToUpper());
                    ddscDt.setColumnValue("SERIAL_CLASS", SERIAL_CLASS.ToUpper());
                    ddscDt.setColumnValue("SERIAL_YEAR", SERIAL_YEAR);
                    ddscDt.setColumnValue("SERIAL_NO", serialNo);
                    ddscDt.setColumnValue("crt_id", "system");
                    ddscDt.setColumnValue("crt_date", System.Convert.ToString(DateTime.Now.ToString("yyyyMMdd")));
                    ddscDt.setColumnValue("crt_time", System.Convert.ToString(DateTime.Now.ToString("HHmmss")));
                    ddscDt.setColumnValue("upd_id", "system");
                    ddscDt.setColumnValue("upd_date", System.Convert.ToString(DateTime.Now.ToString("yyyyMMdd")));
                    ddscDt.setColumnValue("upd_time", System.Convert.ToString(DateTime.Now.ToString("HHmmss")));
                }
                else
                {
                    //如果有找到同一年度的資料，則更新流水號
                    serialNo = System.Convert.ToString(System.Convert.ToInt32(ds.Tables["B2B_SERIAL"].Rows[0]["SERIAL_NO"]) + 1);
                    //ddscDt.addNewRow()
                    ddscDt.setColumnValue("action", "upd");
                    ddscDt.setColumnValue("SERIAL_NO", serialNo);
                    ddscDt.setColumnValue("upd_id", "system");
                    ddscDt.setColumnValue("upd_date", System.Convert.ToString(DateTime.Now.ToString("yyyyMMdd")));
                    ddscDt.setColumnValue("upd_time", System.Convert.ToString(DateTime.Now.ToString("HHmmss")));
                    ddscDt.setWhereValue("SERIAL_TYPE", SERIAL_TYPE.ToUpper());
                    ddscDt.setWhereValue("SERIAL_CLASS", SERIAL_CLASS.ToUpper());
                    ddscDt.setWhereValue("SERIAL_YEAR", SERIAL_YEAR);
                }
                ddscDs.addDataTable(ddscDt);
                DataTable dtb = ddscDs.getDataSet().Tables[0];
                string[] sqlCmd = sqlConvert.convertToSQL(dtb);
                dtb.Dispose();
                dsReturn = dba.runExecuteNonQuery(sqlCmd);
                int i = 0;
                string zero = "";
                for (i = 1; i <= SERIAL_LENGTH - serialNo.Length; i++)
                {
                    zero = zero + "0";
                }

                result = SERIAL_CLASS + SERIAL_YEAR + zero + serialNo;

            }
            catch (Exception)
            {

            }
            finally
            {
                dsReturn.Dispose();
                ddscDt = null;
                ddscDs = null;
            }
            return result;

        }

        //單筆寫檔使用方法
        public void InsertMATERIAL_IMAGE(string CODE, string SERIAL_NO, byte ImageData)
        {
            dbConnection dba = new dbConnection();
            System.Data.OracleClient.OracleConnection myConnection = dba.getOracleClientConnection();
            System.Data.OracleClient.OracleCommand myCommand = myConnection.CreateCommand();
            string strSQL = "UPDATE B2B_MATERIAL_IMAGE_SEQUENCE SET IMAGE=:IMAGE WHERE CODE=:CODE AND SERIAL_NO=:SERIAL_NO  ";

            try
            {
                myConnection.Open();
                myCommand.CommandText = strSQL;

                System.Data.OracleClient.OracleParameter param1 = myCommand.Parameters.Add("IMAGE", System.Data.OracleClient.OracleType.Blob);
                param1.Direction = ParameterDirection.Input;
                param1.Value = ImageData;
                System.Data.OracleClient.OracleParameter param2 = myCommand.Parameters.Add("CODE", System.Data.OracleClient.OracleType.VarChar);
                param2.Direction = ParameterDirection.Input;
                param2.Value = CODE;
                System.Data.OracleClient.OracleParameter param3 = myCommand.Parameters.Add("SERIAL_NO", System.Data.OracleClient.OracleType.VarChar);
                param3.Direction = ParameterDirection.Input;
                param3.Value = SERIAL_NO;
                myCommand.ExecuteNonQuery();

            }
            catch (System.Data.OracleClient.OracleException ex)
            {
                throw (ex);
            }
            finally
            {
                myConnection.Close();
            }
        }

        //新增圖片
        public void InsertMATERIAL_IMAGE(string CODE, Array SERIAL_NO, ArrayList ImageData)
        {
            dbConnection dba = new dbConnection();
            System.Data.OracleClient.OracleConnection myConnection = dba.getOracleClientConnection();
            System.Data.OracleClient.OracleCommand myCommand = myConnection.CreateCommand();
            System.Data.OracleClient.OracleTransaction myTrans = default(System.Data.OracleClient.OracleTransaction);
            string strSQL = "UPDATE B2B_MATERIAL_IMAGE_SEQUENCE SET IMAGE=:IMAGE WHERE CODE=:CODE AND SERIAL_NO=:SERIAL_NO  ";
            int i = 0;
            try
            {
                myConnection.Open();
                myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                // Assign transaction object for a pending local transaction
                myCommand.Transaction = myTrans;
                myCommand.CommandText = strSQL;
                for (i = 0; i <= ImageData.Count - 1; i++)
                {
                    if (((Array)(ImageData[i])).Length > 1)
                    {
                        System.Data.OracleClient.OracleParameter param1 = myCommand.Parameters.Add("IMAGE", System.Data.OracleClient.OracleType.Blob);
                        param1.Direction = ParameterDirection.Input;
                        param1.Value = ImageData[i];
                        System.Data.OracleClient.OracleParameter param2 = myCommand.Parameters.Add("CODE", System.Data.OracleClient.OracleType.VarChar);
                        param2.Direction = ParameterDirection.Input;
                        param2.Value = CODE;
                        System.Data.OracleClient.OracleParameter param3 = myCommand.Parameters.Add("SERIAL_NO", System.Data.OracleClient.OracleType.VarChar);
                        param3.Direction = ParameterDirection.Input;
                        param3.Value = SERIAL_NO.GetValue(i);
                        myCommand.ExecuteNonQuery();
                    }

                }
                myTrans.Commit();
            }
            catch (System.Data.OracleClient.OracleException ex)
            {
                myTrans.Rollback();
                throw (ex);
            }
            finally
            {
                myConnection.Close();
            }
        }
        //*********************************************************************
        //
        // SendMail() 函式
        //
        // 公用函式，傳入Mail之主題、收件者、寄件者、內文，後送出電子郵件
        //
        //*********************************************************************
        public bool SentMail(string MailSubject, string MailTo, string MailCc, string MailBcc, string MailFrom,
            string MailBody, string[] aryAttachment = null)
        {
            try
            {
                //SIT時將所有MAIL寄送給東鉅
                //MailTo = ";stanhuang@evaair.com;";
                //MailTo中不能以;結尾
                if (MailTo.StartsWith(";"))
                {
                    MailTo = MailTo.Substring(1);
                }
                if (MailTo.EndsWith(";"))
                {
                    MailTo = MailTo.Substring(0, MailTo.Length - 1);
                }

                MailMessage Message = new MailMessage();
                String[] toList = MailTo.Split(new char[]{';'});
                //MailFrom, "", MailSubject, MailBody
                Message.From = new MailAddress(MailFrom);
                Message.Subject = MailSubject;
                Message.Body = MailBody;
                foreach(String address in toList){
                    Message.To.Add(address);
                }
                
                if (!string.IsNullOrEmpty(MailCc))
                    Message.CC.Add(new MailAddress(MailCc));
                if (!string.IsNullOrEmpty(MailBcc))
                    Message.Bcc.Add(new MailAddress(MailBcc));
                Message.BodyEncoding = System.Text.Encoding.UTF8;
                Message.IsBodyHtml = true;
                if (aryAttachment != null)
                {
                    for (int Counter = 0; Counter <= aryAttachment.Length - 1; Counter++)
                    {
                        Attachment attachment = new Attachment(aryAttachment[Counter]);
                        Message.Attachments.Add(attachment);
                    }
                }
                //SMTP認證帳號
                //Message.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", txtID.Text)
                //SMTP認證密碼
                // Message.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", txtPW.Text)
                SmtpClient client = new SmtpClient(System.Configuration.ConfigurationSettings.AppSettings["SMTP"]);
                client.Send(Message);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }


        //added by Ray 取得EmpID的所有AD資訊
        public System.DirectoryServices.PropertyCollection QueryADInfo(string strEmpID)
        {
            //2011/9/26 modi walter  P3511M0148 CRWD040之發送訊息
            string DOMAIN = "";
            if (strEmpID.Trim().ToUpper().StartsWith("A"))
            {
                DOMAIN = System.Configuration.ConfigurationManager.AppSettings["AD_UNI"] + "/dc=uniair,dc=com,dc=tw";
            }
            else
            {
                DOMAIN = "LDAP://evaair.com/dc=evaair,dc=com";
            }
            DirectoryEntry objDirectory = new DirectoryEntry(DOMAIN);

            //Const DOMAIN As String = "evaair.com/dc=evaair,dc=com"
            //Dim objDirectory As New DirectoryEntry("LDAP://" & DOMAIN)
            //-end
            DirectorySearcher objSearcher = new DirectorySearcher(objDirectory);
            SearchResult objResult = default(SearchResult);
            System.DirectoryServices.PropertyCollection pvReturn = null;
            Exception exError = null;

            objSearcher.SearchScope = SearchScope.Subtree;
            //Modify By EmilyLi On 20131226 - 修正AD 環境調整，抓取AD資料效能很慢問題
            //objSearcher.ReferralChasing = ReferralChasingOption.All;
            objSearcher.ReferralChasing = ReferralChasingOption.None;
            objSearcher.Filter = string.Format("(&(objectClass=user)(sAMAccountName={0}))", Public_Decode(Public_Encode(strEmpID)));

            try
            {
                objResult = objSearcher.FindOne();
                pvReturn = objResult.GetDirectoryEntry().Properties;
            }
            catch (Exception ex)
            {
                exError = ex;
            }
            finally
            {
                objSearcher.Dispose();
                objDirectory.Dispose();

                if (exError != null)
                {
                    throw (exError);
                }
            }

            return pvReturn;
        }

        //added by Ray Get AD Info what you want
        public string QueryADInfo(string strEmpID, ADQueryType queryType, string strPropertyName = "")
        {
            object strReturn = default(object);

            try
            {
                System.DirectoryServices.PropertyCollection pvInfo = QueryADInfo(strEmpID);
                if (queryType == ADQueryType.DisplayName)
                {
                    strReturn = pvInfo["DisplayName"].Value;
                }
                else if (queryType == ADQueryType.DeptCode)
                {
                    strReturn = pvInfo["EVAAIR-Employee-FinanceDeptCode"].Value;
                }
                else if (queryType == ADQueryType.DistinguishedName)
                {
                    strReturn = pvInfo["distinguishedName"].Value;
                }
                else if (queryType == ADQueryType.TelNumber)
                {
                    strReturn = pvInfo["telephoneNumber"].Value;
                }
                else if (queryType == ADQueryType.Email)
                {
                    strReturn = pvInfo["mail"].Value;
                }
                else if (queryType == ADQueryType.Specify)
                {
                    strReturn = pvInfo[strPropertyName].Value;
                }
            }
            catch (Exception ex)
            {
                strReturn = "ERROR - " + ex.Message;
            }

            return System.Convert.ToString(strReturn);
        }

        //added by Ray 取得指定陣列中指定屬性的AD資訊
        public string[] QueryADInfo(string strEmpID, string[] queryAttributes)
        {
            if (queryAttributes == null)
            {
                return null;
            }

            string[] arrReturn = new string[queryAttributes.Length - 1 + 1];

            try
            {
                System.DirectoryServices.PropertyCollection pvInfo = QueryADInfo(strEmpID);
                int n = 0;

                foreach (string attribute in queryAttributes)
                {
                    try
                    {
                        arrReturn[n] = (string)(pvInfo[attribute].Value);
                    }
                    catch (Exception ex)
                    {
                        arrReturn[n] = "ERROR - " + ex.Message;
                    }
                    n++;
                }
            }
            catch (Exception ex)
            {
                for (int i = 0; i <= queryAttributes.Length - 1; i++)
                {
                    arrReturn[i] = "ERROR - " + ex.Message;
                }
            }

            return arrReturn;
        }

        public string Public_Encode(string myString)
        {
            return HttpUtility.HtmlEncode(myString);
        }

        public string Public_Decode(string myString)
        {
            StringWriter myWriter = new StringWriter();
            HttpUtility.HtmlDecode(myString, myWriter);
            return myWriter.ToString();
        }
    }

    //取得AD資訊類型
    public enum ADQueryType
    {
        DisplayName,
        DeptCode,
        DistinguishedName,
        TelNumber,
        Email,
        Specify
    }

}
